topic Re: Set Analysis with two must condition value in QlikView

Set Analysis with two must condition value

Kailash_Shaw — Mon, 20 Jun 2022 09:16:35 GMT

Hello!

Hope you are doing well.

I have this scenario. I am looking for the Sum(UnitSales) for Customer where Product must be AA and BB. A Customer may have more than these two products but Sum should be of only AA & BB. If it is either of them, expression should ignore those customers.

I have used below expression. I have got the Sum for Customer which has only AA & BB. But not for who has more than these two.

'sum({<Customer = e({<Product /= {'AA','BB'}>}Customer)>}UnitSales)'

Would much appreciate if someone can help me with it.

Re: Set Analysis with two must condition value

Sujy15 — Mon, 20 Jun 2022 10:10:11 GMT

Hi @Kailash_Shaw ,

Please try the below expression :
Sum(Total <Customer> {<Product={'AA','BB'}>} UnitSales)

Hope it may helps you!

Thanks!

Re: Set Analysis with two must condition value

Kailash_Shaw — Mon, 20 Jun 2022 10:35:14 GMT

Thanks @Sujy15,
It is working fine for Customer with more than these (AA, BB) products but not with either of them.
Here Betacab number is reflecting perfect, i.e. just for AA, BB but for Canutility it is reflecting just for AA. Because it has only just this one product. Canutility should be ignored in this case.

Re: Set Analysis with two must condition value

saranyadurai — Mon, 20 Jun 2022 12:31:47 GMT

Hi @Kailash_Shaw ,

Try like this,

Sum({<Customer=p({<Product={'AA','BB'}>})-p({<Product-={'AA','BB'}>})>}Values)

Thanks,

Saranya

Re: Set Analysis with two must condition value

Kailash_Shaw — Mon, 20 Jun 2022 12:52:09 GMT

Thanks for response @saranyadurai,

This is also got me the same result as the first (mine) one.

Re: Set Analysis with two must condition value

MarcoWedel — Mon, 20 Jun 2022 19:03:54 GMT

Sum({$<Customer=p({$<Product={'AA'}>})*p({$<Product={'BB'}>})>} UnitSales)

Re: Set Analysis with two must condition value

Kailash_Shaw — Tue, 21 Jun 2022 08:26:00 GMT

Thanks for response @MarcoWedel,

It is quite close but taking Product 'CC' into consideration for Customer Betacab.

Re: Set Analysis with two must condition value

MarcoWedel — Tue, 21 Jun 2022 17:07:52 GMT

forgot to restrict Products as well ...

Sum({$<Customer=p({$<Product={'AA'}>})*p({$<Product={'BB'}>}), Product={'AA','BB'}>} UnitSales)

Re: Set Analysis with two must condition value

Kailash_Shaw — Wed, 22 Jun 2022 08:12:12 GMT

Thanks so much @MarcoWedel !!