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

[bhaskarsharma03]

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.

[preminqlik]

//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

[preminqlik]

have you tried above solution ?