value based on previous column

Question

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,

Bren_Spill2 · Accepted Answer

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

If it does help, please accept as solution.

Thanks!

Gawa · Accepted Answer

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