topic Pivot table with 'others' label in App Development

Pivot table with 'others' label

Anonymous — Fri, 05 Aug 2016 13:28:10 GMT

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)

Re: Pivot table with 'others' label

vinieme12 — Fri, 05 Aug 2016 16:16:48 GMT

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)

Re: Pivot table with 'others' label

jpenuliar — Fri, 05 Aug 2016 16:38:48 GMT

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

Re: Pivot table with 'others' label

jpenuliar — Fri, 05 Aug 2016 16:50:56 GMT

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

)

Re: Pivot table with 'others' label

Anonymous — Wed, 10 Aug 2016 10:14:39 GMT

Thanks Vineeth,

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

Regards,

Sílvia