topic Re: Rolling 12 month in a bar chart in Visualization and Usability

Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 10:21:24 GMT

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?

Re: Rolling 12 month in a bar chart

Gysbert_Wassenaar — Fri, 02 Nov 2018 10:35:13 GMT

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).

Re: Rolling 12 month in a bar chart

juraj_misina — Fri, 02 Nov 2018 10:41:05 GMT

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

Re: Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 11:52:56 GMT

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

Re: Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 12:10:54 GMT

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

Re: Rolling 12 month in a bar chart

Gysbert_Wassenaar — Fri, 02 Nov 2018 12:30:57 GMT

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).

Re: Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 12:32:25 GMT

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

Re: Rolling 12 month in a bar chart

juraj_misina — Fri, 02 Nov 2018 12:52:35 GMT

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

Re: Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 14:15:50 GMT

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

Re: Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 14:16:32 GMT

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

Re: Rolling 12 month in a bar chart

simonbowers — Fri, 02 Nov 2018 14:37:44 GMT

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

Re: Rolling 12 month in a bar chart

juraj_misina — Fri, 02 Nov 2018 22:04:46 GMT

Can you perhaps share an example app which we can check?

Re: Rolling 12 month in a bar chart

juraj_misina — Fri, 02 Nov 2018 23:27:32 GMT

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.

Re: Rolling 12 month in a bar chart

simonbowers — Wed, 07 Nov 2018 10:09:29 GMT

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.