community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

Liquidity Planning | From Actual to Plan

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_EURDataType
2019-09-0125125511.25Actual
2019-09-01-23451.56Plan
2019-10-01-11235111Plan
2019-11-01352155611.23Plan
2019-12-01-2052521

Plan

 

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_EURDataType
2019-09-0125125511.25Actual
2019-10-01-11235111Plan
2019-11-01352155611.23Plan
2019-12-01-2052521

Plan

 

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
DataType
2019-09-0125125511.2525125511.25Actual
2019-10-01-1123511113890400.25Plan
2019-11-01352155611.23366046011.48Plan
2019-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

Quasar

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..Capture.PNG

Meteoroid

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).

Capture10.PNG

Hey  @neilgallen  and @alexcordero :

 

Thank you so much for your quick help on this, it worked out perfectly and I can now show a graph with the actuals as a line and on the dual axis also the forecasted liquidity!

 

Have a great day and thank you again!

Kind regards,

 

Alex

Labels