Qlik Sense App Development

Contributor II

Pivot table with 'others' label

Hi,

Imagine this:

II have a table named table1

- Category

- Product

- Quantity

Category          Product          Quantity

A                         1                    10

A                         2                    20

A                         3                    30

B                         4                    40

B                         5                    50

B                         6                    60

And a table named table 2:

- Protocol

- Product

Protocol          Product

P1                    1

P2                    2

I want to bluid a pivot table.

My dimensions are:

- Category;

- Product

My measures are:

- Sum(Quantity)

My filter is going to be Protocol.

So when I select Protocol P1, I want that my Pivot table shows:

Sum(Quantity)

Category A     ----------           60

1                  ---------           10

But I also want to show all the other Products that are associated with Category A, represented with an "Others" label

Example:

Sum(Quantity)

Category A     ----------           60

1                   ---------           10

Others         ---------           50

And if I select Product 1 and 2 the results I want is:

Sum(Quantity)

Category A     ----------          60

1                   ---------           10

2                   --------             20

Others         ---------            30

Is this Possible? How?

Let me know if you need more to understand my problem.

Regards,

Sílvia

(PS: this is kinf off related with this Post: Get associate field of selection)

Tags (1)
1 Solution

Accepted Solutions
Esteemed Contributor II

Re: Pivot table with 'others' label

See Attached

TAB1:

Category,Product,Quantity

A,1,10

A,1,30

A,2,20

B,2,30

A,3,30

B,4,40

B,5,50

B,6,60

];

Protocol,Product

P1,1

P1,3

P1,5

P2,2

P2,6

P2,4

];

Chart dimension:

=if(isnull(aggr(if(wildmatch(Product,Product),Product),Product)),'Others'

,Product)

Chart Expression:

sum(TOTAL <Product> {<Protocol=>} Quantity)

4 Replies
Esteemed Contributor II

Re: Pivot table with 'others' label

See Attached

TAB1:

Category,Product,Quantity

A,1,10

A,1,30

A,2,20

B,2,30

A,3,30

B,4,40

B,5,50

B,6,60

];

Protocol,Product

P1,1

P1,3

P1,5

P2,2

P2,6

P2,4

];

Chart dimension:

=if(isnull(aggr(if(wildmatch(Product,Product),Product),Product)),'Others'

,Product)

Chart Expression:

sum(TOTAL <Product> {<Protocol=>} Quantity)

Valued Contributor III

Re: Pivot table with 'others' label

Hi Silvia,

try this :

Expression:

=If(Dimensionality() <> 1, sum(Quantity), If(sum({1}Quantity)- sum(Quantity) = 0, sum({1}Quantity), sum({1}Quantity) - sum(Quantity)))

Label for Totals (Presentation Property)

=if(sum({1}Quantity)-sum({1}Quantity) = 0,'TOTAL','OTHERS')

Should look like below:

I still have to figure out to exclude Category B

Valued Contributor III

Re: Pivot table with 'others' label

Hi Silvia,

Try below expression:

=If(Dimensionality() <> 1

, sum(Quantity)

, If(sum({1}Quantity)- sum(Quantity) = 0,

sum({1}Quantity)

,sum({1<Category = p(Category)>} Quantity) - sum(Quantity))

)

Contributor II

Re: Pivot table with 'others' label

Thanks Vineeth,

With a few tweaks to the expression to consider other required fields, it worked perfectly

Regards,

Sílvia