Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
jekbauita
New 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)
2 Replies
Highlighted
jekbauita
New Contributor III

Re: Pivot Table + Aggregation on total data

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. 

Highlighted
MVP & Luminary
MVP & Luminary

Re: Pivot Table + Aggregation on total data

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

 

Unbenannt.JPG

- Marcus