Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

da_pagnott
New Contributor III

Column with percentage on measure Pivot table

Hi guys,

Can any please help mem I'm stuck with a percentage column..

I have a pivot table like the one below and I need to add a column with the % of each supplier for each customer:

Customer           Supplier                # items by supplier             %

A                          S1                         10                                          I'd like to get 10/(10+5) ; I get 10/(10+5+12+22)

A                          S2                          5                                           I'd like to get 5/(10+5) ; I get 5/(10+5+12+22)

B                          S2                          12                                         I'd like to get 12/(12+22) ; ...

B                          S3                           22                                        I'd like to get 22/(12+22) ; ...

 

To count the number of items i use this formula:

=count(distinct ITEMS)

 

For the % colums I tried in this way but I get a wrong result because I don't get the right % calculated for the total items for each customer, but I get a percentage based on the total number of items:

=count(distinct ITEMS)/count(distinct total ITEMS)

 

Do you have any suggestion for me?

 

Thank you in advance!

Tags (1)
Labels (4)
1 Solution

Accepted Solutions
Highlighted
Channa
Valued Contributor III

Re: Column with percentage on measure Pivot table

Count(Items)/Count(TOTAL <Customer> Items)

 

if not add second dimension

 

Count(Items)/Count(TOTAL <Customer,Supplier> Items)

 

try it will help else we will try AGGR

AGGR(Count( Items),Customer)

Channa
2 Replies
Highlighted
Channa
Valued Contributor III

Re: Column with percentage on measure Pivot table

Count(Items)/Count(TOTAL <Customer> Items)

 

if not add second dimension

 

Count(Items)/Count(TOTAL <Customer,Supplier> Items)

 

try it will help else we will try AGGR

AGGR(Count( Items),Customer)

Channa
da_pagnott
New Contributor III

Re: Column with percentage on measure Pivot table

Awesome! It was so simple!!

Your first suggestion works!

 

I put into the field the Customer, to have the right percentage, and also the week info and now every week has the right %.

=count(distinct ITEMS)/count(distinct total <Customer, Week number> ITEMS)

 

Thank you!