Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I need to build a table that has a column that calculates the sum of sales for the last 12 months for each month. Meaning if I have a row for April 2021, the sum should include all months between and including April 2020 until April 2021.
Here is a small example:
// Loading sample data
Data:
NoConcatenate Load
Date(AddMonths(MonthStart(Today()), 1-RecNo()), 'YYYY MMM') as RelativeMonth,
123 as CODE,
RecNo() as Units
AutoGenerate 50;
// Create intervals that every code contributes to
Intervals:
NoConcatenate Load Distinct
RelativeMonth as IntervalStart,
Date(AddMonths(RelativeMonth, 11), 'YYYY MMM') as IntervalEnd,
CODE,
Units as RollingSumUnits
Resident Data;
// Join interval keys to data
Join(Data) IntervalMatch(RelativeMonth, CODE) Load
IntervalStart,
IntervalEnd,
CODE
Resident Intervals;
// Join remaining fields from interval table to data
Join(Data) Load * Resident Intervals;
Drop Table Intervals; // Table is no longer needed
// Load the final table, calculate the rolling sum
Final:
NoConcatenate Load
RelativeMonth,
CODE,
Units,
Sum(RollingSumUnits) as Units12MRolling
Resident Data
Group By
RelativeMonth,
CODE,
Units;
Drop Table Data; // Table is no longer needed
IntervalMatch might be what you were looking for.
Hi! Thanks for the quick reply. I have checked out the link you send, but I don't fully understand how to apply it on my script. Could you help me out with that?
Here is a small example:
// Loading sample data
Data:
NoConcatenate Load
Date(AddMonths(MonthStart(Today()), 1-RecNo()), 'YYYY MMM') as RelativeMonth,
123 as CODE,
RecNo() as Units
AutoGenerate 50;
// Create intervals that every code contributes to
Intervals:
NoConcatenate Load Distinct
RelativeMonth as IntervalStart,
Date(AddMonths(RelativeMonth, 11), 'YYYY MMM') as IntervalEnd,
CODE,
Units as RollingSumUnits
Resident Data;
// Join interval keys to data
Join(Data) IntervalMatch(RelativeMonth, CODE) Load
IntervalStart,
IntervalEnd,
CODE
Resident Intervals;
// Join remaining fields from interval table to data
Join(Data) Load * Resident Intervals;
Drop Table Intervals; // Table is no longer needed
// Load the final table, calculate the rolling sum
Final:
NoConcatenate Load
RelativeMonth,
CODE,
Units,
Sum(RollingSumUnits) as Units12MRolling
Resident Data
Group By
RelativeMonth,
CODE,
Units;
Drop Table Data; // Table is no longer needed