Waterfall volume data

Question

Hello, I am very new to Alteryx - 1 week old and this is my first post.

I've made some great progress on a number of fronts but this issue has me stumped... When I do this in Excel, I have to create a reasonably complex formula, I doubt sharing that will help.

Referring to the pic pasted below - I created two simple tables and used the Union tool to create the apricot colored area on the left.

I have illustrated the desired output on the right hand side in green.

Effectively as we roll across the columns (from Period 1 of 2023 to period 12 2023) on each row, the PO (purchase order) volume is called off against each period according to the volume figure for that period as given by Record row 1. I'm hoping the illustration makes clear the desired logic (effectively columns adding to 4000/4000/5000... and rows adding to 7000).

Can this be done in Alteryx?

Many thanks!

Liam

Waterfall Purchase Order Volume against Usage.yxmd

Waterfall Data Usage and POs.xlsx

Liam-Cervin · Answer

Hi Clifford, thanks very much for the tips.

I've had a close look at the 3 creating macro videos (standard, batch and iterative) and also the deploying macros video among other results in the community.

I can sense that the solution is in this area but conceptually I'm still a little lost. I have strong intuitions on how to tackle anything complex in Excel but that doesn't really help me in Alteryx and though I can see the terrific power of Alteryx and it's many advantages as with most things the learning curve presents challenges.

If you or anyone can reveal more of how to go about a combination of the iterative and batch macros that would be great.

It seems that I need to calculate two numbers to zero before proceeding to the next: i.e. (1) remaining PO MT = 0 (noting consumption occurs over more than one financial period, and the next purchase order needs to commence at that mid-point of the financial period that the 'just used' PO became fully consumed), and (2) remaining financial period MT = 0 (at which point the remaining volume of the 'active' PO is consumed against the subsequent period). The original image in my first post visually illustrates this.

Any help much appreciated!

Liam

Clifford_Coon · Answer

Hi @Liam-Cervin ,

I think I'd approach it with an "Batch Macro" inside of an "Iterative Macro"

Something like:

The comunity training has good info for both macro types.

Happy Solving ;-)