Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Rolling past 3 months average for previous 13 months (based on selected month)

Hi,

I need to create a chart that shows past 13 months in dimension (If Feb 2018 is selected, then the dimension should show months from Jan 2017 to Jan 2018); and for each of these 13 months, I need to calculate the past 3 months Sales average in the expression.

I have attached sample data. Could anyone please help me with the set expression?

Thank you.

2 Replies
Highlighted
Specialist II
Specialist II

Re: Rolling past 3 months average for previous 13 months (based on selected month)

//Script:

CM_PM_Table:

LOAD DISTINCT

       MonthYear_UI,

       MonthYear, // KEY to connect master calendar

       Date_MonthYear_UI,

       if(MonthYear = MonthYear_UI,'Current','Previous')                  as CM_PM_Flag;

LOAD  DISTINCT

       MonthYear                                                          as MonthYear_UI,

       Date(MonthYear)                                                    as Date_MonthYear_UI,

Date(Date#(subField(Date(MonthYear,'MMM-YY')&'_'& Date(AddMonths(MonthYear,-1)&'_'&Date(AddMonths(MonthYear,-2),'MMM-YY'),'_'),'MMM-YY'),'MMM-YY') as MonthYear

RESIDENT  MasterCalendar ;

//connect above table with master calendar using MonthYear as a key.

in UI create two variables below:

vCM_PM_StartDate =Date(addmonths(monthstart(max(Date)),-12);

vCM_PM_EndDate=Date(max(Date));





Take Dimension : MonthYear_UI


and Expression :

sum({<Date_MonthYear_UI={'>=$(vCM_PM_StartDate)<=$(vCM_PM_EndDate)'},MONTH=,YEAR=,PERIOD=,QUARTER=,MONTHYEAR=,MonthYear>}Sales)/3

//in above expression make sure you bypass all your UI calendar filters.

Regards

Prem kumar THANGALLAPALLY

Highlighted
Specialist II
Specialist II

Re: Rolling past 3 months average for previous 13 months (based on selected month)

have you tried above solution ?