topic Rolling 3 Months average in QlikView

Rolling 3 Months average

Fri, 18 Nov 2011 08:49:57 GMT

Hey Folk,

I have a requirment in which i have to show rolling 3 Months Average in a Bar chart.

Logic: For each month, average should be of previous 3 months including that month.

Example: If Month is Feb-11, then average against Feb in Bar chart should be of Dec-10, Jan-11 and Feb-11.

Likewise it should happen for every month in the Chart in dimension.

Here is the logic which I am trying to implement:

=(sum([Total Doses])

+Sum({<YearMonth ={"$(=Date(addmonths(YearMonth, -2),'MMM-YY'))"}>}[Total Doses])

+Sum({<YearMonth ={"$(=(addmonths(Max(YearMonth), -1)))"}>}[Total Doses]))/3

Hoping for solutions....!!!

Rolling 3 Months average

SunilChauhan — Fri, 18 Nov 2011 08:55:12 GMT

use

avg({<YearMonth={">=$(=Date(addmonths(YearMonth, -2),'MMM-YY'))

<=$(=Date(addmonths(YearMonth, 0),'MMM-YY'))"}>}

[Total Doses])

avg({<YearMonth={">=$(=Date(addmonths(max(YearMonth), -2),'MMM-YY'))

<=$(=Date(addmonths(max(YearMonth), 0),'MMM-YY'))"}>}

[Total Doses])

Re: Rolling 3 Months average

Fri, 18 Nov 2011 09:19:23 GMT

Thanks Sunil for your reply.

I have tried the formula given by you, but unfortunately its again not serving the purpose.

In the dimension all the months should be visible without any selection.

With that formula, with no selection, no data is there in the chart.

The moment I am clicking on some month, its showing 3 rolling months in the dimension.

For your reference I have attached a dummy app.

Rolling 3 Months average

Anonymous — Fri, 18 Nov 2011 09:32:35 GMT

use this in your attached app.

Sum({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -2), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), 0), 'MMM-YY'))"} >} Sales)

Re: Rolling 3 Months average

Fri, 18 Nov 2011 09:44:01 GMT

Thanks alfasierra,

With this formula I am getting 3 Rolling months of Max(YearMonth) only .

Which means, with no selection I am only getting 3 values in dimension.

But all Values shouls come in Dimension.

Rolling 3 Months average

Anonymous — Fri, 18 Nov 2011 10:04:58 GMT

hi,

don't understand wath u exactly want .... rolling 3 month for me is see only 3 month...

if u want to see all chanche expression with

Sum({<YearMonth ={">=$(=Date(addmonths(Min(YearMonth), -0), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), 0), 'MMM-YY'))"} >} Sales)

is that wath u want?

Rolling 3 Months average

Anonymous — Fri, 18 Nov 2011 10:15:53 GMT

Or maybe like this?

Sum({<YearMonth ={">=$(=Date(addmonths(Min(YearMonth)), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), 0), 'MMM-YY'))"} >} Sales)

Re: Rolling 3 Months average

Fri, 18 Nov 2011 10:46:17 GMT

Hey Just check the attached file.

I hope it would be clear to you then what I am exactly looking for...!!!

Thanks

Re: Rolling 3 Months average

Fri, 18 Oct 2013 08:14:00 GMT

In this situation , what i always do is that: add a field named 'Last3MonthsAvg' in the Fact table. This field represents the average sales of last 3 months for the smallest dimensions. In the chart, you sum the values of this field.

i made a test qv file in the attachment and it works.

This is what i do , anybody have other ideas?

thanks

zhou

Re: Rolling 3 Months average

Gysbert_Wassenaar — Fri, 18 Oct 2013 08:30:26 GMT

If you're using month as a dimension and you want 3-month rolling averages then set analysis alone won't suffice. See this document for an explanation and solutions: Calculating rolling n-period totals, averages or other aggregations

Re: Rolling 3 Months average

tresB — Fri, 18 Oct 2013 08:48:12 GMT

Hi,

There would be several ways, one (in the script) is like attached sample. PFA