13 Replies Latest reply: Nov 7, 2018 5:09 AM by Simon Bowers RSS

    Rolling 12 month in a bar chart

    Simon Bowers

      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?