Pivot table with 'others' label

[Anonymous]

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)

[vinieme12 · Accepted solution]

See Attached

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]

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]

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

          )

[Anonymous]

Thanks Vineeth,

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

Regards,

Sílvia