Overlay 2 rolling 12 month charts in one

[snehamahaveer]

Hi All,

My latest requirement is to create a chart that shows two rolling 12 month as a comparison of one another.

I am able to show them separately as 24 month period. However, when I overlay them by month as dimension I am unable to sort them based on the latest month values. The chart automatically sorts from Jan - Dec or Dec-Jan and not by the latest month to the last 12 months.

Can someone please help with this

Thanks in advance.!

[snehamahaveer]

Hi Sunny,

Yes, that is correct. Thank you so much for your effort to solve this.

S

[sunny_talwar · Accepted solution]

Try this

=Num(Month(Date#(Only({1} Month), 'MMM'))) - Num(Month(Date#(Only(TOTAL Month), 'MMM'))) + 1 +

If(Num(Month(Date#(Only({1} Month), 'MMM'))) - Num(Month(Date#(Only(TOTAL Month), 'MMM'))) + 1 <= 0, 12, 0)

[marcus_malinow]

Sorry, no. If your dummy app isn't an accurate reflection of your real world app then we're going to struggle to help you with this.

[snehamahaveer]

Thank you so much Sunny. Your formula does the job!

[gaddeonline]

Hi Sunny,

I know this issue is closed. But I couldn't find any other posts close to my issue. Can you please tell me where should I place the above expression. Is it in sort expression? As we are in Apr 2023, my rolling 12 months are from Apr 2022 to Mar 2023. And my Rolling 12 months prior year is Apr 2021 to Mar 2022. I need the measure to be compared between these two time periods. As you can see in the below screenshot, rather than from Jan to Dec, the order of the months on the chart should be from Apr to Mar and it should be changed every month.

The closest solution I have achieved is in the below screenshot. in the month dim of 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 you please help me to achieve this?

Thanks and Regards

Mahesh Gadde

[gaddeonline]

Hi Everyone,

@stevedark provided the solution for the above issue I raised. It is available in the below post.

https://community.qlik.com/t5/New-to-Qlik-Sense/Overlay-2-rolling-12-month-measures-in-one-chart/m-p...

I tweaked little bit and here is the script for last two rolling years.

LET vDateMin = Num(AddMonths(MonthStart(Today()),-24));
LET vDateMax = Num(AddMonths(MonthEnd(Today()),-1));

tmpDate:
LOAD
Date($(vDateMin)+RowNo()-1, 'DD MMM YYYY') as Date
AUTOGENERATE 1
while $(vDateMin)+IterNo()-1<= $(vDateMax);

Dates:
LOAD
*,
Dual(Month(MonthYear), (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(AddMonths(MonthEnd(Today()),-1)) * 12) + Month(AddMonths(MonthEnd(Today()),-1)) - MonthNo as [Months Back];

LOAD
Rand() * 1000 as Value,
Date,
Date(MonthStart(Date), 'MMM YYYY') as MonthYear,
(Year(Date) * 12) + Month(Date) as MonthNo,
Month(Date) as [Month Name]
RESIDENT tmpDate;

DROP TABLE tmpDate;

Thanks and Regards

Mahesh Gadde