Previous 12 Month Usage Total

[cmcclea4]

Hi Alteryx Community,

I have a list of the usage history for parts.(one record of data contains part name, usage quantity, usage date. A part can have multiple usage records for one month). I am trying to compile a historical snapshot of the parts at the end of each month for the past several years. One of the fields I need to calculate is 'Prior 12 month usage history' in which I sum up the usage quantity for each part. The tricky part is that I need a formula or tool that can sum up the previous 12 month usage for a part for a month from the past.

For example, if the monthly report is for April 2020, Part A was used 20 times from April 2019-April 2020 so the 'prior 12 month usage history' would be 20. If the monthly report is for September 2018, the prior 12 month usage history column would include all usages between Sep 2017-sep 2018. I need both the April report and September report in the historical snapshot report.

I'm struggling with a method for summing not just the previous 12 month usages, but the previous 12 month usages for past months.

If anyone could help me with the best technique for tackling this challenge it would be much appreciated! I'd be happy to clarify/expand on the issue.

Thanks!

[cplewis90 · Accepted solution]

Hey @cmcclea4,

You should be able to use a multi-row formula tool to achieve this (https://help.alteryx.com/current/designer/multi-row-formula-tool). You would need to specify you the number of periods you want to sum across and also make sure you sort your data correctly.

Once you have done the sort and adding the multi-row formula tool then you can just create a simple formula (grouped by your part number or part indicator) and it will sum the last 12 months for you. If you have a sample data set you could share I can create a workflow as an example.

[jeff_reynolds]

@cplewis90 I actually solved this with a macro, but your way is simpler I think. Simpler is usually better. 

In the attached workflow, there is a list of 143 months. I created a new field called 'Use' using RandInt, then used your method to sum up the current row / month and the previous 11. 

Workflow works as needed, so hopefully this points OP in the right direction. 

Good luck.