3M rolling average for data less than 3 months old

sifatnabil — Thu, 02 Apr 2015 03:57:25 GMT

Hi all,

I have a 3M (or 12 week) rolling average in a text box, but it doesn't work for data less than 3 months old because it always averages the last 3 months even if the product was only launched 1 month ago.

e.g. this is the formula I use in a texbox:

sum([REVENUE])-(Sum({<[CALENDAR DATE]={'>=$(=MonthStart(Max([CALENDAR DATE]), -2))<=$(=MonthEnd(Max([CALENDAR DATE])))'}>}

[REVENUE])/(3))

(Sum({<[CALENDAR DATE]={'>=$(=MonthStart(Max([CALENDAR DATE]), -2))<=$(=MonthEnd(Max([CALENDAR DATE])))'}>}

[REVENUE])/(3))

keeps taking the last 3 months even if data does not exist for that product (i.e. assumes zero revenue for months where the product was not launched). This messes up the 3M average. What's the way around this?

topic 3M rolling average for data less than 3 months old in QlikView

3M rolling average for data less than 3 months old