I'm trying to understand how to create a rolling 12 month calculation in a bar chart. This is for Qlik Sense NOT QlikView.
What I want to see if a chart that show the count of the 12 months moving through time. So January 16 would be reflective of the count Feb 15 - Jan 16, then the next bar Feb '16 to reflect of the period Mar 15 to Feb 16 etc.
To try and understand how to do this I've stripped by data back to pull only the fields I need for this calculation in a new app. I figure once I understand how to do it I can then use an expressions/scripting in the full app I actually need it for. This means I have two field in my load script, Date Received and Case Type. I have also created a master calendar based on Joss Good's post. When multiple users on the forums reference one guide on how to create a master calendar then it makes sense to use it!
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
// Calendar for date received
Temp:
Load
min(DateReceived) as minDate,
max(DateReceived) as maxDate
Resident Problemmaster;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
ReceivedCalendar:
Load
TempDate AS DateReceived,
week(TempDate) As RWeek,
Year(TempDate) As RYear,
Month(TempDate) As RMonth,
Day(TempDate) As RDay,
YeartoDate(TempDate)*-1 as RCurrentYTDFlag,
YeartoDate(TempDate,-1)*-1 as RLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RRC12,
date(monthstart(TempDate), 'MMM-YYYY') as RMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as RQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as RWeekYear,
WeekDay(TempDate) as RWeekDay,
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
What I'm finding is that in #1 KPI the expression =Count({<CaseType={'Internal'},RRC12={'-1'}>}CaseType) works great and reflect the previous 12 months. However this only works for a single data point. What I can't work out is how to do this with moving data points. I suspect its a rangesum I need I just can't get the expression right.
Can anyone help?
.png "Luminary Alumni"){kind=icon}
