Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
dboughzala
Contributor
Contributor

Using Aggr and total in pivot table

Hello Everyone,

A new user of Qlik Sense and I have been trying to solve this problem but in vain. So here it is, maybe you can help :

I have 3 dimensions: Market,Retailer and Content Field. For each, I have a weight, for example for :

  • Market = France
  • Retailer =Amazon
  • Content Field=Bullet Count
  • Weight = 15%. 

I want to create a  pivot table where I have Market and retailers as dim-rows and content Filed as dim-column. My measure would be taking this into account: if for example "bullet count" is not tracked on one retailer so its weight (15%) is reallocated to other content fields. In that case, the "images count" weight is no longer 25% (25/100) but 29,4% (25/85).

I have tried many formulas, I am stuck at having the first sous-measure which is the total of weights per Market and Retailer, It shows me always 100, which is the maximum and obviously not the case for all retailers. However, when I select a retailer, I get the exact amount. Here some screenshots:

When I don't select anything ( I have been testing on a flat table):

dboughzala_0-1664807649413.png

when I select a retailer:

dboughzala_1-1664807675176.png

The forumla I used:

 

sum(total aggr(sum(aggr(max(weights),content_field)),Market,[e-shop]))

 

Thank you for your help.

 

Labels (3)
1 Solution

Accepted Solutions
dboughzala
Contributor
Contributor
Author

Hello,

Thank you for you reply but no it does not work 😕  sum (total weights) will always be the same number for all markets and retailers, which is not the case always. 

I tries this and now it works: 

sum(weights)/sum(total <Market,[e-shop]>weights)

 Thank you,

Dorra

View solution in original post

2 Replies
rubenmarin

Hi, have you tried this?: Sum(weights)/Sum(Total weights)*100

dboughzala
Contributor
Contributor
Author

Hello,

Thank you for you reply but no it does not work 😕  sum (total weights) will always be the same number for all markets and retailers, which is not the case always. 

I tries this and now it works: 

sum(weights)/sum(total <Market,[e-shop]>weights)

 Thank you,

Dorra