topic Re: Last 12 Month Average in QlikView

Last 12 Month Average

Thu, 05 Sep 2013 19:59:47 GMT

I am trying to build an inventory app and I need to be able to produce the average inventory by brand for the last 12 months in a chart. When I build the formula I get the 12 month average, but at the item level, so my data looks like this:

Month 1 Month 2 Month 3 Average

Brand A - 100,000 200,000 150,000 1,500 (based on item level averages versus total brand averages)

What formula can I use to the average of the last 12 months inventory for the total brand, which in this case would be 150,000?

Re: Last 12 Month Average

Oleg_Troyansky — Thu, 05 Sep 2013 20:35:50 GMT

Looks like you need to use advanced aggregation with AGGR:

1. Summarize inventory numbers by Brand and Month

2. Average out the monthly totals and present the information by Brand.

So, assuming that Brand is a Chart Dimension (and Month is not), your formula could looks along those lines:

AVG( AGGR ( sum(Inventory), Brand, YearMonth))

, with an addition of the 12-month condition and any other conditions.

If you need to show a historical trend of 12-month averages, then you need to use a technique called "As of date". I just wrote a blog about it:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

best,

Oleg Troyansky

Re: Last 12 Month Average

whiteline — Thu, 05 Sep 2013 20:46:28 GMT

Hi.

Something like this:

=Avg(aggr(Sum([Amount]), Brand))