Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jekbauita
Contributor III
Contributor III

Pivot Table + Aggregation on total data

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:

Schermata 2020-02-14 alle 16.35.31.png

 

 

 

 

 

 

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

Schermata 2020-02-14 alle 16.49.31.png

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. 

Labels (4)
1 Solution

Accepted Solutions
jekbauita
Contributor III
Contributor III
Author

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. 

View solution in original post

4 Replies
jekbauita
Contributor III
Contributor III
Author

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
MVP & Luminary
MVP & Luminary

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

 

Unbenannt.JPG

- Marcus

 

 
 

 

 

 

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
jekbauita
Contributor III
Contributor III
Author

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. 

View solution in original post