Qlik sense

[tetew89]

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.

[LRuCelver]

IntervalMatch might be what you were looking for.

[tetew89]

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?

[qv_testing]

Try this 

find max date in your table and left join to table

Left Join(DateTable)

LOAD Distinct

      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

if(Date#(date(Date_Max,'YYYYMMDD'),'YYYYMMDD')>=DATE(AddMonths(MonthStart(DATE#($(varMax_Date ),'YYYYMMDD')),-12),'YYYYMMDD'),1,0) as R12M

and in your expressions write R12M={1}

Hope this help!

[LRuCelver · Accepted solution]

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