Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Pivot table with 'others' label

See Attached

pivotOthers.JPG

LOAD SCRIPT:

TAB1:

LOAD * INLINE [

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

];

LOAD * INLINE [

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
vinieme12
Esteemed Contributor II

Re: Pivot table with 'others' label

See Attached

pivotOthers.JPG

LOAD SCRIPT:

TAB1:

LOAD * INLINE [

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

];

LOAD * INLINE [

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)

jpenuliar
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

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

          )

silviaganhao
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

Community Browser