topic Re: Select if count is zero in QlikView

Select if count is zero

Thu, 10 Jul 2014 11:29:50 GMT

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...

Re: Select if count is zero

ali_hijazi — Thu, 10 Jul 2014 11:41:55 GMT

Check attached

Re: Select if count is zero

Thu, 10 Jul 2014 11:48:54 GMT

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)

Re: Select if count is zero

ali_hijazi — Thu, 10 Jul 2014 11:51:40 GMT

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

Re: Select if count is zero

sudeepkm — Thu, 10 Jul 2014 12:11:25 GMT

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.

Re: Select if count is zero

Thu, 10 Jul 2014 13:08:34 GMT

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!

Re: Select if count is zero

ali_hijazi — Thu, 10 Jul 2014 13:14:37 GMT

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

Re: Select if count is zero

Thu, 10 Jul 2014 13:19:59 GMT

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

Re: Re: Select if count is zero

sudeepkm — Thu, 10 Jul 2014 13:21:32 GMT

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.

Re: Re: Select if count is zero

Thu, 10 Jul 2014 13:30:26 GMT

Excellent! Thank you!

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