Compare Rolling Twelve Month Period in Qlik

Creating a line chart in Qlik showing a year on year comparison is very easy, using the Month and Year functions to create two dimensions. What if you want to show a rolling twelve month comparison though? This is not quite so obvious, but this post makes it just as easy.

The Challenge

The question was posed on Qlik Community how to overlay two 12 month measures in one chart. Using set analysis it is straight forward to have two measures which cover the correct date ranges, but when the Month dimension is used it will sort by calendar month, rather in the order required for rolling periods. You can see the question and my response in this post.

The response uses a technique I have used a number of times before but it occurred to me that I have not documented it on the blog, so here it goes.

Creating Rolling Month and Year Dimensions

This code can either sit in your master calendar code, should you use one, or you can place it directly in the load statement from source data. Typically I will go for the latter approach.

The first thing that we need to do in the load script is to create a sequential integer for each month. This then allows us to work out the number of calendar months between any two dates. This technique has many more uses than just rolling twelve month analysis and is an incredibly useful dimension to have in your data model.

If we do (Year(Date)*100)+Month(Date) we will get a sequential integer value within a year (e.g. as at now it will give 202305), but the deltas break when you go across a year (e.g. comparing 202212 to 202301). In order to fix this we can just multiply the year by 12 and add the month. So, again taking May 23 as an example you will get 24,281. This is obviously not at all useful as a human readable number (just as telling someone that the date today is 45051 is only useful if they are a machine), but it means that the numbers for Dec 22 (24,276) and Jan 23 (24,277) are now just one integer apart.

In the load script we can put this code in to create a new field called MonthNo like this:

(Year(Date) * 12) + Month(Date) as MonthNo,

Now we have that value we need to work out how many months back any given month is from today. The code that we used on the Date field can also be done on the today() function, giving a comparable figure for the current date. Using a preceding load, we can compare that to the MonthNo field to get a value for Months Back, like this:

(Year(today()) * 12) + Month(Today()) - MonthNo as [Months Back]

This new field in itself may be useful in your charts and filters within Sense, for instance it could make your set analysis for a chart showing the last twelve months much easier as you do not need to deal with date formats in the set code.

Jumping up another level of preceding load we can now use the Months Back value to create the Rolling Month and Rolling Year values.

The Month function gives us a dual data-typed value with the three letter month name and the integer order for the month from 1 to 12. The text part of that dual datatype is great for us but the number does not give us what we want.

We can use the Dual function though to build our own dual data-typed value with the correct sequential number. Using the output of the Month function as the first parameter of the Dual statement will only take the text part of that value. The correct sequential number can be calculated by working out how many months back the start of the year was and then taking away the months back for the date in question.

The whole of that code looks like this:

Dual(Month(Month), (Floor([Months Back]/12)*12)-[Months Back]) as [Rolling Month],

We can get the number of rolling years back from our Months Back value by taking the integer part of the number of months back divided by 12. In order to turn that into text to show as a dimension we want to suffix it with the text “Years Back”. As having “0 Years Back” looks a bit naff, we need to have anything in the past 12 months showing as “Current Year”. Having that text means that sorting on the field breaks (as it would if you have more than a decade of data), so we need to use the dual function again.

Putting all of that together gives us this statement:

Dual(if([Months Back] < 12, 'Current Year',
    Floor([Months Back]/12) & ' Years Back'),
    Floor([Months Back]/12)) as [Rolling Year]

We then have the two dimensions that we can use in a line chart:

Putting It All Together

This code generates a whole bunch of dates and then applies the date code described above in a resident load. You can copy and paste this to a new app and see the code working in isolation, or your can take parts of it to apply to your own data.

tmpDate:
LOAD
    Date(today()-RowNo(), 'DD MMM YYYY') as Date
AUTOGENERATE(2000);

Dates:
LOAD
    *,
    Dual(Month(Month),
        (Floor([Months Back]/12)*12)-[Months Back]) as [Rolling Month],
    Dual(if([Months Back] < 12, 'Current Year',
        Floor([Months Back]/12) & ' Years Back'),
        Floor([Months Back]/12)) as [Rolling Year]
    ;
LOAD
    *,
    (Year(today()) * 12) + Month(Today()) - MonthNo as [Months Back]
    ;
LOAD
    Rand() * 1000 as Value,
    Date,
    Date(MonthStart(Date), 'MMM YYYY') as Month,
    (Year(Date) * 12) + Month(Date) as MonthNo,
    Month(Date) as [Month Name]
RESIDENT tmpDate
;

DROP TABLE tmpDate;

Something else you might want to deal with when looking at rolling months is a run rate value for the current month, which takes into account how far through the month we are and what the value will be if extrapolated to the number of days in the month. I have covered this in a previous post.

Not only does this code give you dimensions that you can use in your charts, the same dimensions can be used in set analysis also. To create the variance to prior rolling twelve months gauge at the top of the chart you can now just use this code:

(Sum({<[Rolling Year]*={'1 Years Back'}>}Value) /
Sum({<[Rolling Year]*={'Current Year'}>}Value)) -1

An example QVF file including this code and some example charts can be downloaded from the Qlik Community post.

I would be very interested to hear from you on this technique and whether you have used similar or can suggest other ways it could be used. Please use the comments below if you would like to add your thoughts.