Last 12 Month Average

[#1]

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?

[Oleg_Troyansky]

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

[whiteline]

Hi.

Something like this:

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