basic noob problem

[mgirdwood]

Hi

I have a set of data that i calculate using an Alteryx workflow, the outputs are monthly costs for projects with certain identifying criteria, for instance below. I am sure this should be a simple answer, but how would I calculate the change in cost between the months where the first 4 columns are the identifier.  I would need to run this for each previous month during the year as I receive each months new data.

Any pointers would be very welcome

Month   Project   Country   Type   Cost
Jan       1           UK             hours 100
Jan       1           DE              hours 100
Jan       2           UK              hours 100
Jan       3           UK              hours   100
Feb       1          UK              hours   200
Feb       1          DE              hours  150
Feb        2         UK              hours   300
Feb        4         FR             Spend   100
Mar        1        UK              hours    250
Mar        4         FR              Spend   200

[BS_THE_ANALYST · Accepted solution]

@mgirdwood 

Is this what you're after? If not could you explain what else needs to be considered? I sorted the rows to collate the groups of rows. Each group/block are the combo of Project/Country/Type. I then sorted the months numerically and calculated the difference between the current month and the previous month. If the previous month didn't exist, it's evaluated to null. Of course, this can be replaced with 0 etc.

I've attached my current solution. Please note, the way you supplied the data in the question took an extra step to parse out. if you're interested  I've put this in its own box.I've annotated the workflow for guidance:

 

[mgirdwood]

@BS_THE_ANALYST   
Thanks for the quick reply,  that seems to be where I am trying to get to on first inspection - could you share the Multi Field configuration as this is not a node I am very familiar with.  Then I will be clearer on the outputs in my main file

Cheers

[mgirdwood]

@BS_THE_ANALYST 
Perfect:    That does seem to have done the trick.  I very much appreciate the help.  It definitely has highlighted some issues in our numbers . .     

...something for my morning 

Cheers

[BS_THE_ANALYST]

@mgirdwood No worries! Hopefully you managed to download the solution. If there's any questions, please don't hesitate to reach out.

The tool I used was called the Multi-Row. The Multi-Row is used to reference rows above and/or below the existing cell + it has that "grouping" functionality that allows it to consider data in "blocks". The Multi-Field is my favourite tool but I didn't use it here. It allows you to apply a single expression to multiple fields and either overwrite them or create a new copy of each of them. It's also got a hidden goodie where you can write to an existing field and change the datatype of that field all in one tool!