Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

value based on previous column

adampolcz
5 - Atom

Hi All,

 

I have a very simple problem, but cannot get through.

I have YTD data from P1 to P12 through the year depending on the last month of the year, so now P1 to P2 but later to P12.

The task is to make it MTD, so if you have like 10, 15, 25, 40, 70 it has to be MTD like 10, 5, 10, 15, 30.

This is extreme simple you just take the value of the last month and subtract the value of the previous month.

 

I tried this with the multi field formula tool but I don't know how to refer to the previous column, moreover it does not recognize any Column function.

 

Thanks,

2 REPLIES 2
Bren_Spill
12 - Quasar
12 - Quasar

Hi @adampolcz - try the workflow attached and let me know if it helps.

 

If it does help, please accept as solution.

 

Thanks!

gawa
16 - Nebula
16 - Nebula

hi @adampolcz 

Alteryx is good at process data in vertical direction(Summarize, Multi Row Formula, etc), but not good at process data in horizontal direction, especially if your input data can have dynamic schema(unknow number of columns, for example).

As @Bren_Spill solution shows, it needs to be transformed into vertical shape by Transpose tool, and calculate MTD by Multi Row Formula, and revert to horizontal shapes by Cross Tab. By doing so, you don't need to modify your WF every time input data schema changes(I suppose # of columns will be increasing monthly P1,P2,P3...)
By the way, if your real data has multiple rows, try my WF as attached.

image.png

Labels
Top Solution Authors