Select if count is zero

[#1]

Hi,

I would like to be able to select customers that has purchased no products in year 2012. How is that possible? I have a list box with expression =Aggr(Count(DISTINCT Product),CUSTOMER_ID) but it doesn't work...

[ali_hijazi]

Check attached

[#3]

Is there any more general method?

If I want to do same selection across year 2012 and Team B or I something more complex?

Count(distinct {<CUSTOMER_ID=E({<YEAR={2012}>} CUSTOMER_ID)>} Product)

[ali_hijazi]

add your conditions to the E function in the set analysis as usual

[sudeepkm]

Please take a look at the attachment. I've a straight table created that will show the list of customer not purchased product during 2012.

[#6]

I hoped there would be some syntax that would use current selections (select year 2012 and after that select "0 meetings") instead of hard coding each year (and other required dimensions). But maybe I can work something out with  your examples.

Thanks!

[ali_hijazi]

you can create a variable and set it from the UI and use this variable inside the set analysis

[#8]

That starts to sound right. Would you have any example how to do that?

[sudeepkm · Accepted solution]

Please see the attachment here the year is dynamic.

Based on the year selected it will display the customers if no selections or more than one years selected then it will display the customer (who did not buy product) in max year. you can similarly use it for other dimensions.

[#10]

Excellent! Thank you!

I didn't know you can use selected dimensions like that through variables.