Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement to identify all the sales with a particular product type. Can you please help me to write a set analysis expression. I have tried many but couldn't achieve my requirement. Here is an example:
Order | Product Type | Sales |
---|---|---|
Order1 | Stationary | 200 |
Order1 | Drinks | 50 |
Order2 | Drinks | 30 |
Order3 | Drinks | 40 |
In my requirement I want to identify all the sales with only drinks in them. So my expression output has to be Order2+Order3=70.
Thanks,
mc
=Sum({<[Product Type]={'Drinks'}>} Sales)
And why not 50+30+40 ?
Thanks tresesco. but this expression fetches Order1 as well. I do not want Order1 to be counted.
Thanks,
MC
Then,
=Sum({<Order - ={'Order1'}, [Product Type]={'Drinks'}>} Sales)
The above table is just an example where as real time data has millions of orders and how to identify orders with only Drinks?
PFA
=Sum({Order=e{<[Product Type]-={'Drinks'}>}Order)>}Sales)
Try this or look at my qvw.
Thank you so much Ramkumar Ramagopalan. It is just spot on.
Can you please help me in understanding the usage of "-=" in this expression? I know that it is useful to implicitly exclude the values but couldn't understand in this context.
Once again thanks for your advise.
Cheers,
mc
This is called indirect set analysis.
1. -= excludes the values inside {}.
so [Product Type]-={'Drinks'} Order gets us 'Order1'.
2. Then comes the indirect set analysis
Exclude all records that are ever associated with 'Order1', which eliminates both rows that have 'Order1' as the Order.
3. Now we have only 30, 40 and hence 70 the result.
Hope that helps.
Hi Ramukumar,
Could you please clarify below query.
When I modified the expression to
=Sum({1<order=e({1<[Product Type]-={'Drinks','Stationary'}>}order)>} Sales)
I am getting total as 320, I expected the result to be 70. What is happening here?
I would also like to know how to fetch sales with "Drinks" and "Stationary" in single sale. In this case total has to be 250.
Thanks in advance.