Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
from the table below : i want to get ( in a pivot table ) only the associated products with the selected one i.e : if in my list i select 'book' -> my pivot table should display only pen and ruler with their respective quantities .
orderID | product | QTY |
1 | book | 5 |
1 | pen | 7 |
1 | ruler | 2 |
2 | paper | 10 |
2 | pencil | 4 |
3 | rubber | 1 |
any idea how to do it ?
Try this
sum({$ <orderID=P(orderID), product=E(product)>} QTY)
Should take all orderID associated to the product(s) selected but discarding the production selection (=E syntax)
Michael
if I understand you want only product of the same order, try with (see attachment)
2 dimension orderID
product
expression sum({$ <product=, orderID=P(orderID)>} QTY)
i want the product of the same order - the selected one
Hi Mambi,
PFA
Hope this helps
-Sundar
not really what i expected because i.e : if i select 'book' in the list box i want to get in the pivot table only pen and ruler cause they have the same orderID in other way i want to get the associated values with my selection.
Try this
sum({$ <orderID=P(orderID), product=E(product)>} QTY)
Should take all orderID associated to the product(s) selected but discarding the production selection (=E syntax)
Michael
Thanks a lot sir,
i got the same result now by using this formula :
sum({1 <product-={'$(=GetFieldSelections(product))'}, orderID=P(orderID)>} QTY)
but i'm a little confusing because if i use sum({$ <product ... instead of sum({1 <product... it's won't work any explanation ?
sum({$ <product=-{$(=concat(product, ','))}, orderID=P(orderID)>} QTY)
$ represents the records of the current selection; 1 (not needed in above expression) represents the full set of all the records in the application
product=-{$(=concat(product, ','))} represents all products except the selected products (=- not -= they have different meaning)
P() represents the element set of possible values