Dynamic calculating last fifth month data and coming month data

[adarsh2608]

Hello

in report when I run this month which shows last 5 months data from Jan to may  , next month when I run the workflow it should show last 5 months data but the last fifth month column will be sum of feb+ jan month values, similarly I July when I'm running it should show last fifth month column will be sum of Feb+March+Jan remaining will be same as last 4 months from the current month .  fifth month column  calculation with previous month starts on  MAY and reset on next year May.

sample o/p in current month

Group	Jan (2022)	Feb(2022)	March(2022)	April(2022)	May(2022)
data1	1	1	1	1	1
data2	1	1	1	1	1
data3	1	1	1	1	1
data3	1	1	1	1	1
data4	1	1	1	1	1

sample o/p when run it on next month

Group	Jan - Feb (2022)	March	April	May	June
Data1	2	1	1	1	1
Data2	2	1	1	1	1
Data3	2	1	1	1	1
Data4	2	1	1	1	1

[IraWatt]

Hey @adarsh2608,

Here's an example of how I would do this:

Not sure what number you would want in the next month so I just put 1. Can change it here:

Any questions or issues please ask :)
HTH!
Ira

[binuacs]

@adarsh2608 One way of doing with this with the help of dynamic select tool..

The first part (selecting the last 5 months) can be achieved by using the dynamic select tool. You need to adjust the formula in the dynamic tool

The second part is tricky , you need to adjust the code inside the dynamic select tool. Attaching a sample workflow for your reference

[IraWatt]

Awesome solution @binuacs, I need to start thinking about using the dynamic select more !

[adarsh2608]

Hello @binuacs ,

Thank you so much for the response .

Consider May as the beginning of the year . When i run the workflow on this month we get last 4 months and current month data as in column there is no calculation of previous month is required. But  when i'm running the workflow on June , the 1st column which was jan in previous month will calculate with the previous month which would be jan + feb . this calculation of 1st column adding with previous month until when in the month of april 2023  and when i'm running this workflow on the April 2023 the first coulmn will be sum (jan +feb+march+apr+may+jun+july+aug+sep+oct+nov+dec)

let me give sample expected o/p of 1st coulmn in each month when i'm running

Month when i'm running workflow	1st column	2nd column	3rd column	4th column	5th column
May	jan	feb	mar	apr	may
June	jan+feb	mar	april	may	june
July	jan+feb+mar	april	may	june	july
Aug	jan+feb+mar+apr	may	june	july	Aug
Sep	jan+feb+mar+apr+may	june	july	Aug	Sep
OCT	jan+feb+mar+apr+may+june	july	Aug	Sep	Oct
NOV	jan+feb+mar+apr+may+june+july	Aug	Sep	Oct	nov
DEC	jan+feb+mar+apr+may+june+july+Aug	Sep	Oct	nov	Dec
JAN	jan+feb+mar+apr+may+june+july +aug+Sep	Oct	nov	Dec	Jan
FEB	jan+feb+mar+apr+may+june+july +aug+sep+Oct	nov	Dec	Jan	Feb
MAR	jan+feb+mar+apr+may+june+july +aug+sep+oct+Nov	Dec	Jan	Feb	Mar
APRL	jan+feb+mar+apr+may+june+july +aug+sep+oct+nov+Dec	Jan	Feb	Mar	April

I can share you the sample workflow , i was building if you able to help with a formula which can dynamically add the month

if you can help me it would be wonderful, as this is 1st time i'm building with alteryx

[binuacs]

@adarsh2608 It is required more logical updates. I will update the workflow and let you know 

[binuacs · Accepted solution]

@adarsh2608 I have updated the dynamic select to pick the dates dynamically as you mentioned in the above example. The workflow is working fine with my sample file. Please try with your input file and let me know if you see any issues. Please note that inorder to get correct result you need to have data on all the months. if any of the month is missing then you will not get the correct result as the dynamic select fields based on the current month. For example the month of June the fields 4-7th picked for processing and rest of the fields will get added into the other dynamic select tool ie Feb and Jan. 

[FieldNumber] = toNumber(DateTimeFormat(DateTimeToday(),'%m')) + 1 //June
OR
[FieldNumber] = toNumber(DateTimeFormat(DateTimeToday(),'%m')) + 0 // May
OR
[FieldNumber] = toNumber(DateTimeFormat(DateTimeToday(),'%m')) - 1 // Apr
OR
[FieldNumber] = toNumber(DateTimeFormat(DateTimeToday(),'%m')) - 2 // Mar
OR
[FieldNumber] = 1

[adarsh2608]

@binuacs ,

Thank you for the post, after some changes it worked

[binuacs]

@adarsh2608 I’m glad it worked for you. The requirement was a tricky one and it took some days to workout the logic