Rolling Months data

[DNS_007]

Hi, I have the data for months in the format beginning from Jan20 to Jun21. Every month I need to show 12 months of data on rolling basis. For eg. in May'21 I need to show the data from Jul20 to May21. In Jul21, it will be Aug20 to Jun21 and so on. Is there any way to automate this in Alteryx? Thanks. Regards DNS

[atcodedog05]

Hi @DNS_007 

Here is how you can do it.

Workflow:

1. Calculating the current month.

2. Date diff between dates and current month to get months btw. Keeping dates where diff is <=12 months rolling prev 12 months.

Hope this helps : )

[DNS_007]

Hi, Thanks for the response. However I have data in form of Months directly. As mentioned, Jan21, Feb21 and so on, and everytime when i do sum using Summation tool I have to manually go and uncheck the month and add current month. Thanks.

[atcodedog05]

Hi @DNS_007 

You can convert the month to alteryx date format and apply the logic like below.

Workflow:

Hope this helps : )

[shreyanshrathod]

@DNS_007 , are you implying that you have Months (Jan20, Feb20, etc) as different column names? and that you wish to add 12 months rolling?

Or are the month names all in one column and you just wish to filter 12 months rolling ?

Kindly provide a sample and dummy excel data

Regards,

Shreyansh

[DNS_007]

Hi Shreyansh,

Yes thats right. I have columns which have Monthly data as Jan20, Feb20 and so on. I would like to have rolling 12 months for such data.

Thanks.

Regards

DNS

[atcodedog05]

Hi @DNS_007 

Can you provide sample data and expected output so that we can help you out better.

[DNS_007]

Attached is the sample input file.

[atcodedog05]

Hi @DNS_007 

I am not sure about the expected output. I have developed based on my assumption. Provide me the expected output I can help you out with that.

Workflow:

1. Using transpose tool to convert months columns to rows.

2. Using append tool to create combination of months.

3. Using filter to keep only rolling 12 months based on source date column.

4. Using crosstab to create the table.

5. Using formula tool and dynamic rename to convert to the required date format.

Hope this helps : )