Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## Overlay 2 rolling 12 month measures in one chart

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

Labels (4)

• ### expression

2 Solutions

Accepted Solutions

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:
Date(today()-RowNo(), 'DD MMM YYYY') as Date
AUTOGENERATE(2000);

Dates:
*,
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]
;
*,
(Year(today()) * 12) + Month(Today()) - MonthNo as [Months Back]
;
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

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

6 Replies
Creator

Hi,

Have you uncheck the include null value in dimension

it is by default check

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.

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

Then concatenate dates offset by 12 months:

CONCATENATE(OffsetDates)
'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

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:
Date(today()-RowNo(), 'DD MMM YYYY') as Date
AUTOGENERATE(2000);

Dates:
*,
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]
;
*,
(Year(today()) * 12) + Month(Today()) - MonthNo as [Months Back]
;
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

Contributor III
Author

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

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