Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor II

## Qlik sense

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.

Labels (1)
• ### Script

1 Solution

Accepted Solutions
Partner - Creator III

Here is a small example:

``````//	Loading sample data
Data:
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:
RelativeMonth as IntervalStart,
Date(AddMonths(RelativeMonth, 11), 'YYYY MMM') as IntervalEnd,
CODE,
Units as RollingSumUnits
Resident Data;

//	Join interval keys to data
IntervalStart,
IntervalEnd,
CODE
Resident Intervals;

//	Join remaining fields from interval table to data

Drop Table Intervals;	//	Table is no longer needed

//	Load the final table, calculate the rolling sum
Final:
RelativeMonth,
CODE,
Units,
Sum(RollingSumUnits) as Units12MRolling
Resident Data
Group By
RelativeMonth,
CODE,
Units;

Drop Table Data;	//	Table is no longer needed``````
4 Replies
Partner - Creator III

IntervalMatch might be what you were looking for.

Contributor II
Author

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?

Specialist II
Try this
find max date in your table and left join to table
Left Join(DateTable)
date(max(Date),'MM/DD/YYYY') as Date_Max
Resident DateTable;

create variable like this  (this is storing max date in a variable)
let varMax_Date = DATE(peek('Date_Max', 0, 'DateTable'),'YYYYMMDD');

resident load as a main table, include this derived columns

and in your expressions write R12M={1}

Hope this help!
Partner - Creator III

Here is a small example:

``````//	Loading sample data
Data:
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:
RelativeMonth as IntervalStart,
Date(AddMonths(RelativeMonth, 11), 'YYYY MMM') as IntervalEnd,
CODE,
Units as RollingSumUnits
Resident Data;

//	Join interval keys to data
IntervalStart,
IntervalEnd,
CODE
Resident Intervals;

//	Join remaining fields from interval table to data

Drop Table Intervals;	//	Table is no longer needed

//	Load the final table, calculate the rolling sum
Final: