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: 
Not applicable

how to get the total values of dimension attribute ?

Hello,

For instance, I have Four attributes( Country,Category,Product and Sales) and i wanna find the the contribution of each category by country.

I put the Category and Country as dimension then created the expression to find the Contribution(=Num(Sum(Sales)/Sum(TOTAL Sales),'0%')

Sum(TOTAL Sales),'0%')  - this calculate the full total of the row but i wanna calculate TOTAL by country. For my expression i getting like below pivot table:

FRGERUKNLSPAIT
Automotive & Gadgets1%0%0%0%1%0%
Fashion 3%3%3%0%6%1%
Home & Living 4%6%11%1%5%3%
Home Appliances4%9%9%1%8%1%
Lifestyle 0%--0%--
Media, Games & Music0%0%0%0%0%0%
Sports & Outdoors0%2%1%0%2%1%
Toys, Kids & Babies2%3%2%0%2%1%
Travel & Luggage1%1%0%0%1%0%
Total 15%25%27%3%24%7%

but I want the pivot table like shown below:

FRGERUKNLSPAIT
Automotive & Gadgets20%0%0%0%1%0%
Fashion 23%3%3%20%6%1%
Home & Living 4%6%11%1%5%3%
Home Appliances40%9%9%4%8%1%
Lifestyle 0%60%70%0%56%80%
Media, Games & Music0%0%0%0%20%3%
Sports & Outdoors0%2%1%45%2%1%
Toys, Kids & Babies4%5%2%30%2%1%
Travel & Luggage8%15%3%0%1%10%
Total 100%100%99%100%100%

100%

Could you please advise me how to fix this one?

Best

Robert

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

Many thanks for your reply. you logic exactly correct.

=Num(Sum(Sales)/

if(COUNTRY='FR',Sum(TOTAL{$<COUNTRY={'FR'}>}Sales),

if(COUNTRY='GER',Sum(TOTAL{$<COUNTRY={'GER'}>}Sales),

if(COUNTRY='UK',Sum(TOTAL{$<COUNTRY={'UK'}>}Sales),

if(COUNTRY='NL',Sum(TOTAL{$<COUNTRY={'NL'}>}Sales),

if(COUNTRY='SPA',Sum(TOTAL{$<COUNTRY={'SPA'}>}Sales),

Sum(TOTAL{$<COUNTRY={'IT'}>}Sales)

))))),'0%')

above is the answer for my question.

Best

Robert

View solution in original post

2 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Try Sum(Sales)/Sum(TOTAL <Country> Sales)


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

Many thanks for your reply. you logic exactly correct.

=Num(Sum(Sales)/

if(COUNTRY='FR',Sum(TOTAL{$<COUNTRY={'FR'}>}Sales),

if(COUNTRY='GER',Sum(TOTAL{$<COUNTRY={'GER'}>}Sales),

if(COUNTRY='UK',Sum(TOTAL{$<COUNTRY={'UK'}>}Sales),

if(COUNTRY='NL',Sum(TOTAL{$<COUNTRY={'NL'}>}Sales),

if(COUNTRY='SPA',Sum(TOTAL{$<COUNTRY={'SPA'}>}Sales),

Sum(TOTAL{$<COUNTRY={'IT'}>}Sales)

))))),'0%')

above is the answer for my question.

Best

Robert