Pivot Table + Aggregation on total data

[jekbauita]

Hello, 

I've searched the forum but I was unable to find an answer matching my needs. I need a better understanding of the Aggr() mechanism or maybe I'm mistaking and I need to look for another way to solve my issue. 

The main problem I have is that I load specific data on a pivot table and I want to compare the average price for every row to the average price of the category of each article. 

I have a data set like the following:

At some point in my QlikView App I need to have a Pivot table like the following:

That shows just some of the columns and for instance, I don't need the category column to be shown. 

The expression for this example are very simple:

• quant. =sum(Quantity)
• amount =sum([Total amount])
• average price =sum([Total amount]) / sum(Quantity)

What I'm trying to achieve is to add one more column that shows, for each row, the average price of a sale based on: Sales Group - Category (so independent from Document, Costumer, Article, Article Color). I don't know the way to achieve this, because every time I try to use a type of aggregation I don't reach the result I'm trying to have. I think the problem lays in my not-so-deep knowledge of the Aggr() function and the fact that the dimensions I'm trying to aggregate aren't shown in the pivot table. 

Of course, in the real application, instead of simple expression I have more complex ones, using Set Analysis, but I'd love to understand the mechanic for this to work and then I will manage myself to make it work with the real environment. 

Has anyone any suggestion? 

Thanks in advance for every help. 

[jekbauita]

It seems quite a difficult situation the one I have, since I didn't get any suggestion I'm going to compute the values I need in the LOAD script and for the moment I'm going to keep them as "static" values in a support table. 

Thanks for everyone who had a look at it. 

[marcus_sommer]

I think aggr() isn't really suitable in your case else it should be solvable with TOTAL, like:

- Marcus

[Brett_Bleess]

Did the suggestion Marcus provided work for you?  If so, do not forget to return to the thread to close things out by using the Accept as Solution button on his post if using Total worked.  

Here is a Design Blog post on Total that may further explain things too:

https://community.qlik.com/t5/Qlik-Design-Blog/What-does-the-TOTAL-qualifier-do/ba-p/1472990

Regards,
Brett

[jekbauita · Accepted solution]

Hi Marcus, I solved with a computation on the loading side, because the SetExpression I needed within the formula was too complex and I always run out of memory during the loading of the page. 

I will go deeper with knowledge of TOTAL aggregator, which of course I did not know well, but as you pointed is the right strategy for such a situation. 

Thanks for you help.