Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
I've now created the as of month as per the instructions and it is correct on all the data connection. However I get the same issue I had been. If, using filters, I select 2018 it still not back counting. So January only shows Jan, Feb total is Jan+Feb. I just don't get what I'm doing wrong, I have followed every instruction
Can you perhaps share an example app which we can check?
One thought thogh. When you say "I select 2018", is that selection made in regular calendar or the as-of calendar? Because if it is in regular calendar table, then the behaviour you describe is expected.
Thank you. I'll go back and try again. I'm unable to share anything due to rules the company has around data usage and sharing.