Alteryx Designer Discussions

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

how to take the sum of past 12 months dynamically with dynamic column names

vasudhajoshi
6 - Meteoroid

Hi All,

 

I need some advice on how to take the sum of previous 12 months based on the dynamic column name. 

ex- as below

eg1- 

IIf([202112]-[202111]>0 And [202110]=0 And
([202101]
+[202102]+[202103]+
[202104]+[202105]+[202106]+[202107]+
[202108]+[202109]+[202110]
+[202111]+[202012])=0
,[202112]
,0) AS 202112
eg2-
IIf([202001]-[201912]>0 And [201912]=0 And
([201901]+[201902]+[201903]+[201904]+
[201905]+[201906]+[201907]+[201908]+[201909]
+[201910]+[201911]+[201912])=0
,[202001]
,0) AS 202001

 

attaching the sample workflow.

 

Thanks

Vasudha

2 REPLIES 2
Hannah_Lissaman
11 - Bolide

Hi @vasudhajoshi 

 

The simplest way to do this is to use a Multi-Row Formula tool which looks at 12 rows of data, sort and group as in your example, and simply add each of the previous 12 rows together. I have attached an example.

Hannah_Lissaman_0-1589811480818.png

 

vasudhajoshi
6 - Meteoroid

Thanks! I didn't know we can increase the number of rows. 

Labels