Creating Rolling N Month Flag in the script

[Kushal_Chawda]

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.

[manojkulkarni]

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

[Digvijay_Singh]

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!

[Kushal_Chawda]

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/

[Anonymous]

Nice Explanation!! Thank you for sharing

[Mazarinen]

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

/M