topic Re: Use of Aggr in QlikView

Use of Aggr

i_villafranca — Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I need your help here

I have the following set of data:

Month	StoreId	Real	Forecast
01.Jan	1	100	90
01.Jan	2	150	175
02.Feb	1	130	125
02.Feb	2	180	190

What I want to achieve is to have per store ID their forecast accuracy per month [1- (abs(forecast-real)/real))], and then the total per store will be the average of each month. So, something like this:

Month	StoreId	Real	Forecast	Forecast Accuracy
01.Jan	1	100	90	90%
01.Jan	2	150	175	83%
02.Feb	1	130	125	96%
02.Feb	2	180	190	94%

And then, the forecast accuracy per store will be their average per month:

StoreId	Forecast Accuracy
1	93%
2	89%

Up until here... everything clear! The problem is when I try to get the total of both stores. What I want is to get the forecast accuracy per month (including both stores) and then the average:

Month	Real	Forecast	Forecast Accuracy
01.Jan	250	265	94%
02.Feb	310	315	98%
Total			96%

In Qlikview, I have the following table:

As you can see I have managed to get the correct data for each store and the correct data for both stores together, but not in the same expression. The result that I want would be like this:

StoreId	Forecast Accuracy
1	93%
2	89%
Total	96%

Can anyone help me to get it? I am sure I am using correctly the Aggr function, but I think I am missing something to get the result that I want.

Thanks a lot!!

Re: Use of Aggr

sunny_talwar — Thu, 09 Mar 2017 16:21:15 GMT

Try another Avg(Aggr()) on top of your already Avg(Aggr())

Avg(Aggr(Avg(Aggr(1-(fabs(Sum(TREV_FC) - Sum(TREV_Actuals))/Sum(TREV_Actuals)), NumMonth, HotelNameAjustado)), HotelNameAjustado))

or select a total mode of average if this is a straight table

Re: Use of Aggr

sunny_talwar — Thu, 09 Mar 2017 16:31:29 GMT

My bad, try this:

If(Dimensionality() = 0,

Avg(Aggr(1-(fabs(Sum(Forecast) - Sum(Real))/Sum(Real)), Month)),

Avg(Aggr(1-(fabs(Sum(Forecast) - Sum(Real))/Sum(Real)), Month, StoreId)))

Re: Use of Aggr

i_villafranca — Thu, 09 Mar 2017 16:58:56 GMT

Hi Sunny,

Great it works!!

Just for me to understand, what does the "Dimensionality" function exactly?

Of course, everything is more complex than it seems, and now I have to add another dimmension to the exercise (I need a hierarchy dimension that will go from destination to store). Having the dimmension doesn´t work, so I am pretty sure I have to add something to the formula regarding "Dimensionality".

Can you help me one more time?

Thanks!

Re: Use of Aggr

sunny_talwar — Thu, 09 Mar 2017 17:06:20 GMT

Read about dimensionality here:

How to use - Dimensionality()

Also read SecondaryDimensionality() used in Pivot tables: The second dimension... or how to use secondarydimensionality()