This document demonstrates creating Flags in the script to calculate Rolling N Months data.
Same thing can be achieved in various ways using set analysis, rangesum fuction,Accumulation on front end. But if Month or MonthYear field is used in chart or any selection is done on Month or MonthYear , it will be difficult to achieve the Rolling calculation using above methods and sometimes expression becomes quite complex.
So it is better to create the Flags from back end in a master calender itself.
This method will create the flag for rolling months which can then be used in front end for selection.This provides better flexibility for users to select the Rolling periods of his choice or this flags can be used in set analysis.
In the application I have created the dummy data to link to the calender.
// 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');
// 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;
Pease find the attached QVW file
