topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

Wed, 14 May 2014 04:49:40 GMT

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,

Re: Set Analysis Expression

tresB — Wed, 14 May 2014 04:52:36 GMT

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

And why not 50+30+40 ?

Re: Set Analysis Expression

Wed, 14 May 2014 04:54:57 GMT

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

Thanks,

Re: Set Analysis Expression

tresB — Wed, 14 May 2014 04:56:59 GMT

Then,

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

Re: Set Analysis Expression

Wed, 14 May 2014 05:00:09 GMT

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

Re: Set Analysis Expression

Wed, 14 May 2014 05:03:56 GMT

PFA

Re: Set Analysis Expression

Wed, 14 May 2014 05:05:18 GMT

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

Try this or look at my qvw.

Re: Set Analysis Expression

Wed, 14 May 2014 05:13:22 GMT

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,

Re: Set Analysis Expression

Wed, 14 May 2014 05:16:35 GMT

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.

Re: Set Analysis Expression

Wed, 14 May 2014 06:04:34 GMT

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.

Re: Set Analysis Expression

anbu1984 — Wed, 14 May 2014 06:36:51 GMT

=Sum({1<order=e({1<[Product Type]-={'Drinks','Stationary'}>}order)>} Sales)

Here Result of [Product Type]-={'Drinks','Stationary'} is empty and e({1<[Product Type]-={'Drinks','Stationary'}>}order gives all orders. Hence the result is 320

Fetch sales with Drinks and Stationary

=Sum({<order=P({<[Product Type]={'Drinks'}>}order)>*<order=P({<[Product Type]={'Stationary'}>}order)>} Sales)

Re: Set Analysis Expression

Wed, 14 May 2014 08:46:56 GMT

Hope it will help

=sum({<order=P({<[Product Type]={'Drinks','Stationary'}>}order)>}sales)

Re: Set Analysis Expression

Wed, 14 May 2014 21:41:01 GMT

Sorry Shweta Singh your expression didn't work. It returns 320.

Re: Set Analysis Expression

Fri, 16 May 2014 00:41:11 GMT

Hi, anbucheliyan's expression should be able to resolve your other questions. Do you have any other questions?

Re: Set Analysis Expression

Fri, 16 May 2014 01:06:24 GMT

Thank you Ramkumar Ramagopalan . I have few more questions , I will post them soon.

One gain thank you so much for your help.

Thanks,

Re: Set Analysis Expression

Fri, 16 May 2014 05:39:40 GMT

Hi, I hope this may help you

Sum(cost) - if(aggr(count(product_s),order_id)>1,sum(cost),0)