Set Analysis with two must condition value

[Kailash_Shaw]

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.

[Sujy15]

Hi @Kailash_Shaw ,

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

Hope it may helps you!

Thanks!

[Kailash_Shaw]

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.

[saranyadurai]

Hi @Kailash_Shaw ,

Try like this,

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

Thanks,

Saranya

[Kailash_Shaw]

Thanks for response @saranyadurai,

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

[MarcoWedel]

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

[Kailash_Shaw]

Thanks for response @MarcoWedel,

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

[MarcoWedel · Accepted solution]

forgot to restrict Products as well ...

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

[Kailash_Shaw]

Thanks so much @MarcoWedel !!