Rolling 12 month in a bar chart

[simonbowers]

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?

[Gysbert_Wassenaar]

See Calculating rolling n-period totals, averages or other aggregations. This applies ot both Qlik Sense and Qlikview (with the exception of the Accumulation setting that's only available in Qlikview).

[juraj_misina]

Hi,

you have to options.

1. You can created (one of my most common recommendations) The As-Of Table.

2. Add this to your calendar table:

Year(TempDate)*12+Month(TempDate) as %MonthID

Create a variable sMaxMonthID = Max(%MonthID) //with leading = sign

In expression, use Count({<CaseType={'Internal'}, RYear, RMonth, %IDMonth={"<=$(sMaxMonthID)>$(=sMaxMonhtID-12)"}>}CaseType) //apart from RYear and RMonth you need to turn off any other possible user selections in calendar fields.

Hope this helps.

Juraj

[simonbowers]

Thank you for your reply. I had read this piece prior to asking my question. Especially the bit around it not being limited by the dimension as this is one the issue I've encountered.

I'll be honest and say the reason I posted the question was I simply didn't understand what the other post wanted me to do.

I was good with the:

=Rangesum(Above(Count({<CaseType={'Internal'}>}CaseType),0,12))

but as noted in the I've hit the issue of I've data going back 10 years but only want to show the last 2 on the chart. The issue being the dimension.

Do I add the as of month to the master calendar? Does it need a separate calendar creating?

I'm still new to Qlik (and typing code for that matter) so really need a dummies guide on how to complete things

[simonbowers]

Thank you for your answer.

I'll need to learn how to create a variable as I never done this before. As in my reply above I'm very new still to Qlik. I'll learn this and see how I get on

[Gysbert_Wassenaar]

The AsOf table is usually created as a seperate table that's associated with the master calendar table on the Month field (or the Date field if you want to do rolling x days too).

[simonbowers]

Hi Folks,

I really want your help but can I ask you to note this has been posted in the new to QLIK forum and if possible supply any answer with this in mind. Most things I never done or tried to do.

Many thanks

[juraj_misina]

Hi Simon,

never mind the variable at this point. I misunderstood your requirement so it would not help you anyway. Go with the As-Of Table article I posted earlier, it is written in a very easy-to-understand manner and you can follow it step by step to achieve what you need. As Gysbert mentioned, as-of table is a separate table connected to main calendar table (mostly) via the Month field (RMonth in your case). In the output chart you can then replace RMonth with AsOfMonth field, see examples and pictures in the article.

Juraj

[simonbowers]

I wanted to mark both your's and Gysbert's answers as the limiting factor here was my knowledge and not your answers. Going with the ASofMonth table on a separate table to my calendar.

I watched some YouTube videos on creating variables and can see the usefulness once my skill level is higher

[simonbowers]

Thank you for your help. I can only mark one answer correct and both of you have solved the issue.