Show only customers who did order products by state_didbuy and not state_didntBuy

[Ricardo]

Hello,

I try to use alternate state to make it possible for end user to get insights which customer did order specific products (state_didBuy) and did not order specific other products (state_didntBuy).

For example: Show only customers who did order more than 2 drinks, but didn't order any burger this year. 

I only want to show customers in the table for whom criteria apply with sum(of state_didBuy)

Is this possible?

I did include sample qvf

The result with sample data should be only cust2 and cust3

Regards,

[sunny_talwar · Accepted solution]

Try this may be

Sum({<customerNumber = {"=Sum({<productgroup = {'Drinks'}, productcategory, product>} quantity) >= 2 and Sum({<productgroup, productcategory = {'Burgers'}, product>} quantity) = 0"}>} quantity)

[Ricardo]

Hello Sunny,

Thank you for replying so quickly, much appreciated. Your hard coded version does work. Do you know if it is possible to get something like this working with the alternate state filters above the table? So user can make own chooses about which products are bought and which not to have same functionality but then with alternate states. Or isn't this possible?

Kind regards

[sunny_talwar]

You should be able to, but your first condition was the Customer with 2 or more drinks. Will the one state selection be always 2 or greater than 2 and second state will tell that customer with 0? How do you imagine the second condition to be played out?

[Ricardo]

With alternative state option I was indeed not clear enough. I did mean without quantity (>=2) filter, so on left filterbox (state_didbuy)  make selection which products are bought and in right filterbox (state_didntBuy) which are never bought . So in table only customers are shown for who both conditions state_didbuy and state_didntBuy are true. Were in table we show sum of didbuy  selection for each selected product.  

Is this possible in some way?

[Ricardo]

Hello Sunny,

Your set expression does what I need manually. Thanks for this solution.

Sum({<customerNumber = {"=Sum({<productgroup = {'Drinks'}, productcategory, product>} quantity) >= 2 and Sum({<productgroup, productcategory = {'Burgers'}, product>} quantity) = 0"}>} quantity)

With this I gonna try to get it work with a state solution.

Thanks for your time.

Kind regards 

[sunny_talwar]

You can try this

Sum({<customerNumber = {"=Sum({[State_DidBuy]} quantity) >= 1 and Sum({[State_DidntBuy]} quantity) = 0"}>} quantity)

[Ricardo]

Hello Sunny,

Many thanks. The State expression works this way. I was trying it in the wrong direction I think. I was trying to first select set with State_DidBuy and then exclude this set with second set State_DidntBuy. That did not work.

Your way is better to understand and does work !

Thank you again and specially by coming back on this one. 

Much appreciated.

Thanks!