topic Average for Dynamic Range in QlikView

Average for Dynamic Range

Fri, 13 Feb 2015 18:50:47 GMT

I am trying to get a monthly average of Sales Volume between two given dates with 12 months being the max number of months going back.

Any help would be greatly appreciated.

Re: Average for Dynamic Range

Oleg_Troyansky — Fri, 13 Feb 2015 18:57:35 GMT

You need to use nested aggregation here (first accumulate monthly figures and then calculate the average.) You can do it using AGGR:

avg(

AGGR(

sum(Sales),

MonthYear, ChartDim1, ChartDim2

)

Just to clarify - I added ChartDim1 and 2 to signify your chart dimensions ( when you use this formula in the chart)

Now, just add your logic about the date range. If you use Set Analysis, make sure to repeat your SA conditions in both the "inner" and the "outer" aggregation functions - sum() and avg().

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

Re: Average for Dynamic Range

Thu, 19 Feb 2015 14:57:24 GMT

I am not familiar with aggregation funtions. I am looking for a monthly average of "Sales Volume" from a MaxMonth going back the number of months found in colmn8 within my table. If the value in column 8 is greater than 12 months i want it to default to 12.