Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Rolling 3 Months average

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....!!!

Tags (1)
10 Replies
chauhans85
Esteemed Contributor

Rolling 3 Months average

use

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

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

[Total Doses])

or

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

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

[Total Doses])

Not applicable

Re: Rolling 3 Months average

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.

alfasierra
Contributor II

Rolling 3 Months average

hi

use this in your attached app.

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

Not applicable

Re: Rolling 3 Months average

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.

alfasierra
Contributor II

Rolling 3 Months average

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?

alfasierra
Contributor II

Rolling 3 Months average

Or maybe like this?

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

Not applicable

Re: Rolling 3 Months average

Hey Just check the attached file.

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

Thanks

Not applicable

Re: Rolling 3 Months average

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

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


talk is cheap, supply exceeds demand
Community Browser