topic Re: 3 month moving/rolling average in text box? in QlikView

3 month moving/rolling average in text box?

sifatnabil — Thu, 28 Aug 2014 09:09:59 GMT

Is this possible? Usually the rolling averages are shown inside graphs where dimension is defined.

rangeavg(above(sum(Sales),0,3) - how could I define it such that it shows the 3 month average of the current month or week inside a textbox? Thanks.

Re: 3 month moving/rolling average in text box?

sifatnabil — Fri, 29 Aug 2014 00:38:27 GMT

bump.

Re: 3 month moving/rolling average in text box?

jagan — Fri, 29 Aug 2014 02:33:34 GMT

Hi,

Try like this if you have datefield in your app

=(Sum({<MonthDimensionName=, QuarterDimensionName=, YearDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -2))<=$(=MonthEnd(Max(DateDimensionName)))'}>}Sales))/3

Note: MonthDimensionName, QuarterDimensionName, YearDimensionName and DateDimensionName with your actual field names.

Hope this helps you.

Regards,

Jagan.

Re: Re: 3 month moving/rolling average in text box?

JonnyPoole — Fri, 29 Aug 2014 02:37:40 GMT

Here is a sample using a Date Calendar . I use a month index 1...n to define the months in sequential order from oldest to most recent month in the data. The set analysis is then very straightforward. For a 3 month average:

=sum( {$<MonthIndex = {'>$(=vMaxMonthIndex-3)'}>} Sales) / 3