Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
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)
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
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))
)
Thanks Vineeth,
With a few tweaks to the expression to consider other required fields, it worked perfectly
Regards,
Sílvia