Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for
Search instead for
Did you mean:
Contributor

## Use of Aggr

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

4 Replies
MVP

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

MVP

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)))

Contributor
Author

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!

MVP

Read about dimensionality here:

How to use - Dimensionality()

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

Community Browser