Using Aggr and total in pivot table

[dboughzala]

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

when I select a retailer:

The forumla I used:

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

Thank you for your help.

[rubenmarin]

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

[dboughzala · Accepted solution]

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