Creating Rolling N Month Flag in the script

Kushal_Chawda — Wed, 25 Sep 2024 18:45:35 GMT

The rolling months method is frequently utilized in business analytics and financial reporting to provide a more accurate representation of performance over time. This document demonstrates how to create a flag in the script to perform rolling N months analysis.

There are various ways to achieve rolling month analysis in graphs using the rangesum() function in conjunction with the above() function on the front end, along with set analysis. While front-end solutions work well for simpler measures and smaller datasets, they can lead to performance issues or increased complexity in cases of more intricate requirements.

Therefore, it is advisable to create a flag in the script using a master calendar.

This approach will establish a flag in the script for rolling months, which can then be used as a filter or in set analysis measures on the front end

// Load min and max Date from Fact MaxDate: LOAD num(max(FieldValue('Date', recno()))) as MaxDate, num(min(FieldValue('Date', recno()))) as MinDate AUTOGENERATE FieldValueCount('Date'); let vMaxDate= Peek('MaxDate',0,'MaxDate'); let vMinDate= Peek('MinDate',0,'MaxDate'); drop table MaxDate; // Generate Dates using min and max date Cal: LOAD *, MonthName(Date) as MonthYear; LOAD date($(vMinDate)+IterNo()-1) as Date AutoGenerate(1) While $(vMinDate)+IterNo()-1<=$(vMaxDate); MaxMonthYear: LOAD num(max(FieldValue('MonthYear', recno()))) as MaxMonthYear AUTOGENERATE FieldValueCount('MonthYear'); // Variable used to restrict MonthYear to <=current month while looping LET vMaxMonthYear = monthname(Peek('MaxMonthYear',0,'MaxMonthYear')); // Define Rolling N in Inline table. 1 is the default value for current month RollMonth: LOAD * Inline [ RollMonth 1 2 3 6 12 ]; Calender: LOAD * Inline [ junk ]; for i=1 to FieldValueCount('RollMonth') LET vRollMonth= FieldValue('RollMonth',$(i)); Concatenate(Calender) LOAD Date, MonthYear, Rolling_Months, month(Rolling_Months) as Month, Year(Rolling_Months) as Year, if(Flag='Rolling1','CurrentMonth',Flag) as Rolling_Flag where Rolling_Months<=Date#('$(vMaxMonthYear)','MMM YYYY'); LOAD Date, MonthYear, monthname(MonthYear,IterNo()-1) as Rolling_Months, 'Rolling'&$(vRollMonth) as Flag Resident Cal While IterNo()-1<=$(vRollMonth)-1 ; NEXT DROP Tables Cal,MaxMonthYear,RollMonth; DROP Field junk;

Use Rolling_Months as your month selection or dimension in your report when performing month-based analysis. You can also utilize Rolling_Flag either as a filter or as a condition in set analysis to construct the required measure.

Please refer to the attached applications.

Re: Rolling N months

manojkulkarni — Mon, 14 Sep 2015 05:30:26 GMT

very useful application. Rolling Months charts required in most of applications

Re: Rolling N months

Digvijay_Singh — Mon, 14 Sep 2015 07:39:05 GMT

Thanks for sharing this. Pl help me to understand calculation of min and max date from fact table. So far I came across and it works fine by simply using min and max function on the date field to get min and max date from fact. Didn't understand the use of Fieldvalue and FieldValueCount here. Thanks in advance!

Re: Rolling N months

Kushal_Chawda — Mon, 14 Sep 2015 07:44:04 GMT

Hi Digvijay,

This method is the fastest method to calculate Min and Max. You can find the details on below link

http://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/

Re: Rolling N months

Anonymous — Mon, 14 Sep 2015 13:55:53 GMT

Nice Explanation!! Thank you for sharing

Re: Rolling N months

Mazarinen — Fri, 15 Dec 2023 10:53:30 GMT

How do you use the Flag in the measure? The measure I have created in your app isn't working.

article Creating Rolling N Month Flag in the script in Member Articles

Creating Rolling N Month Flag in the script

Re: Rolling N months

Re: Rolling N months

Re: Rolling N months

Re: Rolling N months

Re: Rolling N months