How to calculate rolling 12 months sales

[hbuehanx]

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

[geraldo]

@hbuehanx 

Could you put the desired result to understand better

[hbuehanx]

Hi, I have modified the question. Please let me know if it is still confusing. Thank you!

[Yoshiro_Fujimori]

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.

[hbuehanx]

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?

[Yoshiro_Fujimori · Accepted solution]

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.

[Qiu · Accepted solution]

@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.

[hbuehanx]

Thank you for the guidance. I'll follow your instruction

[hbuehanx]

Thank you for the help. I'll do more research on macro

[NiranjanY]

Hi ACE Spotlight Series: Jifeng Qiu

   Could you please share me batch file for reference.

Thanks for advance...