Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Expression

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:

OrderProduct TypeSales
Order1Stationary200
Order1Drinks50
Order2Drinks30
Order3Drinks40

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

1 Solution

Accepted Solutions
Not applicable
Author

15 Replies
tresesco
MVP
MVP

=Sum({<[Product Type]={'Drinks'}>} Sales)

And why not 50+30+40 ?

Not applicable
Author

Thanks tresesco. but this expression fetches Order1 as well. I do not want Order1 to be counted.

Thanks,

MC

tresesco
MVP
MVP

Then,

=Sum({<Order - ={'Order1'}, [Product Type]={'Drinks'}>} Sales)

Not applicable
Author

The above table is just an example where as real time data has millions of orders and how to identify orders with only Drinks?

Not applicable
Author

PFA


Not applicable
Author

=Sum({Order=e{<[Product Type]-={'Drinks'}>}Order)>}Sales)

Try this or look at my qvw.

Not applicable
Author

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

Not applicable
Author

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.


Not applicable
Author

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.