topic Re: Average Rolling 12 months in QlikView

Average Rolling 12 months

etrotter — Wed, 09 May 2018 19:01:18 GMT

Hello,

I am trying to calculate the rolling 12 month average for market share, but I'm not sure how to go about it.

the calculation for market share is: Sum(Billing Quantity)/Sum(#Units)

Please let me know if you need more information, or have any ideas.

Thanks!

Re: Average Rolling 12 months

sunny_talwar — Wed, 09 May 2018 19:02:22 GMT

I suggest you to look here

The As-Of Table

Re: Average Rolling 12 months

etrotter — Wed, 16 May 2018 16:59:07 GMT

Hi Sunny,

I used the guide, but it still doesn't appear to be working as expected:

The test is supposed to be accumulating. I follow

Here is my script, most of it is copied from the link you sent:

[Master Calendar]:
LEFT Keep (CalendarLinkBox)
LOAD Distinct
Year&Period AS %YearMonth,
Year,
Month as MONTH,
Date(MonthStart(Date),'YYYY MMM') as Month,
FiscalYear&Num(FiscalMonth,'00') as FiscalYearMonth,
Year&Num(Period,'00') as YearMonth,
Year&'-'&Quarter as YearQuarter,
Period,
Day,
Quarter,
PM_Flag,
PQ_Flag,
FiscalYear,
FiscalMonth ,
FiscalPeriod,
FiscalQuarter,
FiscalYearPeriod,
FiscalHalf,
FiscalYearHalf,
FiscalYearQuarter,
FiscalMonthYear,
FiscalMonthName,
MonthYear,
Date(Date,'MM/DD/YYYY') as Date,
Num(FiscalMonth,'00')&'/'&Subfield(Date(Date,'MM/DD/YYYY'),'/',2)&'/'&Subfield(Date(Date,'MM/DD/YYYY'),'/',3) as FiscalDate
FROM
$(qvd_layer3_path)MasterCalendar.qvd(qvd)
// WHERE
// [Year] >= Year(Today())-4
;

// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct Month
Resident [Master Calendar] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
AsOfMonth,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month;

Drop Table tmpAsOfCalendar;

I must be missing something because its not working, Have any ideas?

Re: Average Rolling 12 months

sunny_talwar — Wed, 16 May 2018 17:05:11 GMT

3 things

1) What you are doing will give you full accumulation and not just 12 months accumulations... might be handled using set analysis

2) Use AsOfTable using Date of MonthYear field rather than using Month

3) Create a AsOfMonthYear and use that as your chart dimension

[As-Of Calendar]:
Load Month,
AsOfMonth,

.... as AsOfMonthYear
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month;