Rolling 12 months

[Kylpat]

Good afternoon, 

I have a set of data that in order to mitigate seasonality in the figures from one month to another i would like to compare Last 12 Months period to Last 12 Months -1 and so on in for each month. 

The data i have is monthly and would need to aggregate/group each month into buckets of 12. Example of how would be the mapping:

The mapping is too big and would need to be maintained each month and i am trying to built a more elegant solution. 

I am thinking in assigning a number to each month + year and then using a formula aggregate the multiple month into categories. The challenge i find is easily aggregate the same month in different categories without entering in a manual selection. 

I attach example datasource. The real has 27 months and a lot of granularity. 

Final result would look like Last 12 Month as columns and sales as rows:

Thanks! Feel free to share ideas, i can give them a try.

kind regards,

Kylian

[bbak91]

You will likely benefit from the multi-row tool. It can reference cells above and below the current cell for each calculation. This will be able to take the 12 previous rows in a calculation for each subsequent cell. https://help.alteryx.com/current/designer/multi-row-formula-tool 

[RolandSchubert]

Hi @Kylpat ,

I would create a new period field to sort on (just in case, year/month records are not sorted accordingly) and use a Multi-Row formula tool to calculate the LTM values.

I've attached a sample workflow. Let me know if it works for you.

Best,

Roland

[apathetichell]

In addition to @bbak91's way you can use a BATCH MACRO, which is what I would do. Make real dates.  The starting month is the control parameter. The macro runs a filter on if a date is within 12 months of the control parameter. Then it's summed.

Now with Macro actually attached.

[Kylpat]

Answers are poping up quick! thanks a lot for the feedback. 

The issue i am having, and is my fault as the database shared does not fully show how my database is structured. I have multiple dimension included such as country, customer and product and would need to keep those dimension. 

I will build a database that can share with the community. For the moment i will try and adjust your solutions and find my way out. In the meantime if you have any tips feel free to share. 

Once data is aggregated,  I will be building a pdf report with a total overview comparing LTM periods, followed by a tableau dashboard giving the capability of filtering by dimensions such customer, product and/or country. 

[DawnDuong]

hi @Kylpat 

Your original data structure is already quite "optimal". I guess you are using a TB or something similar to generate LTM sales.

With such data, Batch macro is likely your best friend. Need to take care to make sure not to have "truncated" sum where there are less than 12 months of data - which you can either check by count or by constraining the date range.

Hope the attached helps. You can also edit it further to make it work for a generalised case of Revenue GL too.

Have fun.
Dawn.