Liquidity Planning | From Actual to Plan

Question

Hi all,

we are using Alteryx for aggregation of Liquidity Planning data. At the end of my current workflow I get one output that is my current liquidity (as of end-of-month). The second output is the planned liquidity (sum for one month) for the next 3 months.

The following shows my current output I have to work with - row 1 will always show only the last Actual amount (from end of month, thus from 30/09/2019)

MonthYearAmount_in_EURDataType2019-09-0125125511.25Actual2019-09-01-23451.56Plan2019-10-01-11235111Plan2019-11-01352155611.23Plan2019-12-01-2052521Plan

I would have to do the following two steps:

1. Delete all rows with Plan data that have a date that is the same or older than the Actual date in row 1 (in this case row 2 would have to be eliminated, since this is old data that is still in the input. It could also be more date from e.g. August that would have to be deleted)

After this the output should be as followed - the Plan rows show the planned sum for each month:

MonthYearAmount_in_EURDataType2019-09-0125125511.25Actual2019-10-01-11235111Plan2019-11-01352155611.23Plan2019-12-01-2052521Plan

2. Now I would like to calculate a new column that cumulates all the values row by row. Thus in the first row there would be the original value, row two would show original value + October amount etc.

MonthYearAmount_in_EURCumulated_in_EUR
DataType2019-09-0125125511.2525125511.25Actual2019-10-01-1123511113890400.25Plan2019-11-01352155611.23366046011.48Plan2019-12-01-2052521...

Plan

I would be really thankful if you could help me with this!

Thank you very much in advance and kind regards,

Alex

neilgallen · Accepted Answer

This is easily obtained! The early steps establish your latest Actual row, and then remove any plan row before this. I did it this way to account for future changes in the dataset or a table that may not be correctly formatted in sequence.

The later steps are just calculating the running total.

let me know if there are any questions..

alexcordero · Accepted Answer

Alternative to getting the max date - otherwise pretty much the same methodology.  Would need to adjust if using for more than 1 client at a time or if DataType field values change and your starting point is not in alphabetical order (start vs actual).