Hi All, I am new to Alteryx. Can someone show me how to calculate rolling 12 months sales based on the month by Product Category, Product, Territory? Here is the sample data. In real data, some months may be missing if there is no sales for the product
Product Category | Product | Territory | Month | Sales |
ABC1 | XYZ1 | AB1 | 12/1/2022 | 100 |
ABC1 | XYZ1 | AB1 | 11/1/2022 | 120 |
ABC1 | XYZ1 | AB1 | 10/1/2022 | 120 |
ABC1 | XYZ1 | AB1 | 9/1/2022 | 110 |
ABC1 | XYZ1 | AB1 | 8/1/2022 | 100 |
ABC1 | XYZ1 | AB1 | 7/1/2022 | 90 |
ABC1 | XYZ1 | AB1 | 6/1/2022 | 80 |
ABC1 | XYZ1 | AB1 | 5/1/2022 | 100 |
ABC1 | XYZ1 | AB1 | 4/1/2022 | 100 |
ABC1 | XYZ1 | AB1 | 3/1/2022 | 90 |
ABC1 | XYZ1 | AB1 | 2/1/2022 | 120 |
ABC1 | XYZ1 | AB1 | 1/1/2022 | 130 |
ABC1 | XYZ1 | AB1 | 12/1/2021 | 120 |
ABC1 | XYZ1 | AB1 | 11/1/2021 | 110 |
ABC1 | XYZ1 | AB1 | 10/1/2021 | 115 |
ABC1 | XYZ1 | AB1 | 9/1/2021 | 100 |
ABC2 | XYZ2 | AB2 | 12/1/2022 | 380 |
ABC2 | XYZ2 | AB2 | 11/1/2022 | 420 |
ABC2 | XYZ2 | AB2 | 10/1/2022 | 400 |
ABC2 | XYZ2 | AB2 | 9/1/2022 | 420 |
ABC2 | XYZ2 | AB2 | 8/1/2022 | 320 |
ABC2 | XYZ2 | AB2 | 7/1/2022 | 440 |
ABC2 | XYZ2 | AB2 | 6/1/2022 | 450 |
ABC2 | XYZ2 | AB2 | 5/1/2022 | 470 |
ABC2 | XYZ2 | AB2 | 4/1/2022 | 550 |
ABC2 | XYZ2 | AB2 | 3/1/2022 | 350 |
ABC2 | XYZ2 | AB2 | 2/1/2022 | 430 |
ABC2 | XYZ2 | AB2 | 1/1/2022 | 340 |
ABC2 | XYZ2 | AB2 | 12/1/2021 | 440 |
ABC2 | XYZ2 | AB2 | 11/1/2021 | 550 |
ABC2 | XYZ2 | AB2 | 10/1/2021 | 440 |
ABC2 | XYZ2 | AB2 | 9/1/2021 | 330 |
Desired Result:
Product Category | Product | Territory | Month | Sales | Rolling 12 month total |
ABC1 | XYZ1 | AB1 | 12/1/2022 | 100 | 1260 (Total Sales from 1/1/2022 to 12/1/2022) |
ABC1 | XYZ1 | AB1 | 11/1/2022 | 120 | 1280 (Total Sales from 12/1/2021 to 11/1/2022) |
ABC1 | XYZ1 | AB1 | 10/1/2022 | 120 | 1270 |
ABC1 | XYZ1 | AB1 | 9/1/2022 | 110 | 1265 |
ABC1 | XYZ1 | AB1 | 8/1/2022 | 100 | |
ABC1 | XYZ1 | AB1 | 7/1/2022 | 90 | |
ABC1 | XYZ1 | AB1 | 6/1/2022 | 80 | |
ABC1 | XYZ1 | AB1 | 5/1/2022 | 100 | |
ABC1 | XYZ1 | AB1 | 4/1/2022 | 100 | |
ABC1 | XYZ1 | AB1 | 3/1/2022 | 90 | |
ABC1 | XYZ1 | AB1 | 2/1/2022 | 120 | |
ABC1 | XYZ1 | AB1 | 1/1/2022 | 130 | |
ABC1 | XYZ1 | AB1 | 12/1/2021 | 120 | |
ABC1 | XYZ1 | AB1 | 11/1/2021 | 110 | |
ABC1 | XYZ1 | AB1 | 10/1/2021 | 115 | |
ABC1 | XYZ1 | AB1 | 9/1/2021 | 100 | |
ABC2 | XYZ2 | AB2 | 12/1/2022 | 380 | 4970 |
ABC2 | XYZ2 | AB2 | 11/1/2022 | 420 | 5030 |
ABC2 | XYZ2 | AB2 | 10/1/2022 | 400 | 5160 |
ABC2 | XYZ2 | AB2 | 9/1/2022 | 420 | 5200 |
ABC2 | XYZ2 | AB2 | 8/1/2022 | 320 | |
ABC2 | XYZ2 | AB2 | 7/1/2022 | 440 | |
ABC2 | XYZ2 | AB2 | 6/1/2022 | 450 | |
ABC2 | XYZ2 | AB2 | 5/1/2022 | 470 | |
ABC2 | XYZ2 | AB2 | 4/1/2022 | 550 | |
ABC2 | XYZ2 | AB2 | 3/1/2022 | 350 | |
ABC2 | XYZ2 | AB2 | 2/1/2022 | 430 | |
ABC2 | XYZ2 | AB2 | 1/1/2022 | 340 | |
ABC2 | XYZ2 | AB2 | 12/1/2021 | 440 | |
ABC2 | XYZ2 | AB2 | 11/1/2021 | 550 | |
ABC2 | XYZ2 | AB2 | 10/1/2021 | 440 | |
ABC2 | XYZ2 | AB2 | 9/1/2021 | 330 |
Thank you very much!
Tim
Solved! Go to Solution.
Hi, I have modified the question. Please let me know if it is still confusing. Thank you!
Hi @hbuehanx ,
Here is one way of doing this.
My result shows one more row than your expected result. I think it OK, but if I misss anything, please let me know.
Workflow
Expression in Multi-Row Formula tool
IF IsEmpty([Row+11:Product Category]) THEN Null()
ELSE [Sales] + [Row+1:Sales] + [Row+2:Sales] + [Row+3:Sales] + [Row+4:Sales] + [Row+5:Sales] + [Row+6:Sales] + [Row+7:Sales] + [Row+8:Sales] + [Row+9:Sales] + [Row+10:Sales] + [Row+11:Sales]
ENDIF
Output
I hope this helps.
Hi Yoshiro, thank you for your help. In real data, some months may be missing if the product doesn't have sales in that month. Does it still work?
Hi @hbuehanx ,
I can think of two approaches.
#1. In Multi-Row Formula tool, check if the row is within 12 months or nor.
#2. Add missing rows before calculationg the rolling totals.
#1 is straightforward, but the formula can be lengthy as below:
IF IsEmpty([Row+11:Product Category]) THEN Null()
ELSE [Sales]
+ IF DateTimeDiff([Date],[Row+1:Date],"month")<=12 THEN [Row+1:Sales] ELSE 0 ENDIF
+ IF DateTimeDiff([Date],[Row+2:Date],"month")<=12 THEN [Row+1:Sales] ELSE 0 ENDIF
...
+ IF DateTimeDiff([Date],[Row+11:Date],"month")<=12 THEN [Row+11:Sales] ELSE 0 ENDIF
ENDIF
#2 would be like below.
(1) Generate Rows tool to generate rows including all the months in the period
(2) Join tool to the output with the original tabl
(3) then apply the original workflow.
If you prefer this approach, you may try it by yourself. 😁
Good luck.
@hbuehanx
Maybe we can use a batch macro.
Inside the macro, we will calculate the Time Difference and filter only 12 months younger, no mater it is null or not.
Thank you for the guidance. I'll follow your instruction
Thank you for the help. I'll do more research on macro
Hi ACE Spotlight Series: Jifeng Qiu
Could you please share me batch file for reference.
Thanks for advance...