Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT

Rolling N months

cancel
Showing results for 
Search instead for 
Did you mean: 
Kushal_Chawda

Rolling N months

Last Update:

Sep 20, 2022 1:59:53 PM

Updated By:

Sue_Macaluso

Created date:

Sep 13, 2015 6:44:16 AM

Attachments

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

Tags (1)
Comments
manojkulkarni
Partner - Specialist II
Partner - Specialist II

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
Not applicable

Nice Explanation!! Thank you for sharing

Mazarinen
Contributor II
Contributor II

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

 

/M

Version history
Last update:
‎2022-09-20 01:59 PM
Updated by: