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:
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.
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.