Overlay 2 rolling 12 month measures in one chart

[gaddeonline]

Hi Experts,

Hope you are all good.

I have been dealing with an issue of how to overlay the months on one chart for two measures. In detail, The rolling 12 months and rolling 12 months prior year measures have to be on the same chart to compare them year on year. While doing so, the order of the months are always falling down to Jan to dec sequence. As we are in Apr-2023, the rolling 12 months are Apr2022 to Mar2023 and prior year is Apr2021 to Mar2022. So the order of the months expecting on the chart is Apr to Mar.

The closest solution I have achieved is by using Dual in the month dimension of the chart. I have used the =Dual(CalPolicyEffective_Month, [CalPolicyEffective_%MonthSeqNum]). So it sorted out to Apr to March. But there are couple of unwanted lines in the graph with 0 values.

Can any of you please help me to achieve this. The closest solution is in the below post. But don't exactly the proposed solution should be applied. whether it is in dimension field or in sort expression.

https://community.qlik.com/t5/QlikView-App-Dev/Overlay-2-rolling-12-month-charts-in-one/m-p/1351790#...

Thanks and Regards

Mahesh Gadde

[Rams-]

Hi,

Have you uncheck the include null value in dimension

 it is by default check

[stevedark]

Hi @gaddeonline 

You may find this blog post useful:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

What you can do is load a new dimension which links every date to its offset.

So, after loading all of your data you can do a resident load to join each month to itself:

OffsetDates:
LOAD DISTINCT
   'Latest' as [Offset Type],
   [Month Year],
   [Month Year] as [Offset Month Year]
RESIDENT DataTable
;

Then concatenate dates offset by 12 months:

CONCATENATE(OffsetDates)
LOAD DISTINCT
   'Prior 12 Months' as [Offset Type],
   [Month Year],
   Date(AddMonths([Month Year], 12), 'DD MMM YYYY') as [Offset Month Year]
RESIDENT DataTable
WHERE [Month Year] < AddMonths(today(), -12)
;

You can then use [Offset Month Year] as the first dimension on your chart and [Offset Type] as the second.

The calculation needs to use Set Analysis on [Offset Month Year] to only give you the 12 months you want.

Hope that makes sense.

Steve

[stevedark · Accepted solution]

Hi again @gaddeonline 

After posting my previous response, it occurred to me that there might be a simpler way. This code and the attached QVF illustrates that:

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;

For each date a month number is calculated. The number is relatively meaningless, but it is crucially sequential by month. The same figure can be created for the current month, then one can be taken from the other, giving the number of months ago (0 for now, 1 for last month etc.). Using that we can look at the remainder when dividing that number by 12, this will be a number from 0 to 11 (obvs) and each month name will get the same value. We can also work out how many years back each month is, by how many times 12 will go into that months back number.

Once you have done that you can use [Rolling Month] and [Rolling Year] in your chart.

Hope that all makes sense?

The previous blog post is probably still worth a look at though.

Steve

[gaddeonline]

Hi Steve,

This is amazing. It is exactly what I want to achieve. I tweaked little bit as I don't want the current month in my current year. Thanks for your quick reply and helpful solution. Hope it helps to the other members as well.

Kind Regards

Mahesh Gadde

[stevedark]

Hi @gaddeonline 

Glad it worked out for you. Thanks for letting me know. I'm thinking I might put it up on my blog as a solution.

Steve

[stevedark · Accepted solution]

I've created that blog post, which has a bit more detail on how this technique works, and you can find it here:

https://www.quickintelligence.co.uk/compare-rolling-twelve-month-period-in-qlik/

Steve