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 CategoryProductTerritoryMonthSalesABC1XYZ1AB112/1/2022100ABC1XYZ1AB111/1/2022120ABC1XYZ1AB110/1/2022120ABC1XYZ1AB19/1/2022110ABC1XYZ1AB18/1/2022100ABC1XYZ1AB17/1/202290ABC1XYZ1AB16/1/202280ABC1XYZ1AB15/1/2022100ABC1XYZ1AB14/1/2022100ABC1XYZ1AB13/1/202290ABC1XYZ1AB12/1/2022120ABC1XYZ1AB11/1/2022130ABC1XYZ1AB112/1/2021120ABC1XYZ1AB111/1/2021110ABC1XYZ1AB110/1/2021115ABC1XYZ1AB19/1/2021100ABC2XYZ2AB212/1/2022380ABC2XYZ2AB211/1/2022420ABC2XYZ2AB210/1/2022400ABC2XYZ2AB29/1/2022420ABC2XYZ2AB28/1/2022320ABC2XYZ2AB27/1/2022440ABC2XYZ2AB26/1/2022450ABC2XYZ2AB25/1/2022470ABC2XYZ2AB24/1/2022550ABC2XYZ2AB23/1/2022350ABC2XYZ2AB22/1/2022430ABC2XYZ2AB21/1/2022340ABC2XYZ2AB212/1/2021440ABC2XYZ2AB211/1/2021550ABC2XYZ2AB210/1/2021440ABC2XYZ2AB29/1/2021330 Desired Result: Product CategoryProductTerritoryMonthSalesRolling 12 month totalABC1XYZ1AB112/1/20221001260 (Total Sales from 1/1/2022 to 12/1/2022)ABC1XYZ1AB111/1/20221201280 (Total Sales from 12/1/2021 to 11/1/2022)ABC1XYZ1AB110/1/20221201270ABC1XYZ1AB19/1/20221101265ABC1XYZ1AB18/1/2022100 ABC1XYZ1AB17/1/202290 ABC1XYZ1AB16/1/202280 ABC1XYZ1AB15/1/2022100 ABC1XYZ1AB14/1/2022100 ABC1XYZ1AB13/1/202290 ABC1XYZ1AB12/1/2022120 ABC1XYZ1AB11/1/2022130 ABC1XYZ1AB112/1/2021120 ABC1XYZ1AB111/1/2021110 ABC1XYZ1AB110/1/2021115 ABC1XYZ1AB19/1/2021100 ABC2XYZ2AB212/1/20223804970ABC2XYZ2AB211/1/20224205030ABC2XYZ2AB210/1/20224005160ABC2XYZ2AB29/1/20224205200ABC2XYZ2AB28/1/2022320 ABC2XYZ2AB27/1/2022440 ABC2XYZ2AB26/1/2022450 ABC2XYZ2AB25/1/2022470 ABC2XYZ2AB24/1/2022550 ABC2XYZ2AB23/1/2022350 ABC2XYZ2AB22/1/2022430 ABC2XYZ2AB21/1/2022340 ABC2XYZ2AB212/1/2021440 ABC2XYZ2AB211/1/2021550 ABC2XYZ2AB210/1/2021440 ABC2XYZ2AB29/1/2021330 Thank you very much! Tim Data.xlsx

How to calculate rolling 12 months sales

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

Data.xlsx

Date Time

Help

Time Series

Accepted answers

Yoshiro_Fujimori

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