topic Re: Conditional Count in QlikView

Conditional Count

Thu, 16 Apr 2015 05:55:16 GMT

Hi all

I have a list of Patrons "Patron_ID" (numeric) and a list of products "PRODUCT_ID" (numeric)

At the front end, I would like to have an expression that calculates the number of returning customers, ie customers who purchased more than one product. However, I am struggling with the expression script at the moment. As you can see from the above image, the "Returning Customers" should indicate as "0" as the patron has only bought one show ticket

I've tried the following to no avail:

if(PRODUCT_ID >=2, count(DISTINCT(PATRON_ID)))

//i clicked on a patron who purchased only 1 product, but it still shows the returning customers as "1".

count((if(count (PRODUCT_ID>1),PATRON_ID)))

//error in expression

Any guidance will be much appreciated!

Re: Conditional Count

amit_saini — Thu, 16 Apr 2015 05:58:49 GMT

Try,

Similar below:

=Count(if (match([ECO state],'Define Components','Design Work' ),[ECO state]))

Thanks,
AS

Re: Conditional Count

amit_saini — Thu, 16 Apr 2015 06:00:39 GMT

Also:

=Count(if(PRODUCT_ID >=2, PATRON_ID)

Thanks,
AS

Re: Conditional Count

amit_saini — Thu, 16 Apr 2015 06:02:20 GMT

Also:

Count({$<PRODUCT_ID={">=2"}>}Dimesion)

Thanks,
AS

Re: Conditional Count

Thu, 16 Apr 2015 06:10:16 GMT

Hi Amit

Tried your suggestions but I am still unable to achieve the correct count (still showing as "1" when correct count should be 0)

Attached QVW for your consideration

Re: Conditional Count

stabben23 — Thu, 16 Apr 2015 06:25:26 GMT

Hi,

is this what you are looking for?

sum(aggr(sum(TICKETS_SOLD)>1,PRODUCT_NAME))

Re: Conditional Count

Thu, 16 Apr 2015 06:41:51 GMT

Unfortunately not, as a single patron can purchase multiple tickets for a single product.

I am looking to do a count of the number of patrons who purchase different products, regardless of the quantity of tickets purchased

Re: Conditional Count

gandalfgray — Thu, 16 Apr 2015 06:45:30 GMT

Hi Chay

Try this:

-sum( aggr(count(DISTINCT PRODUCT_ID), PATRON_ID)>1 )

hth/gg

Re: Conditional Count

gandalfgray — Thu, 16 Apr 2015 06:48:49 GMT

The

aggr(count(DISTINCT PRODUCT_ID), PATRON_ID)

part will return the number of distinct products bought by a patron.

The comparison with 1 will return a boolean TRUE, which is the numeric -1, if there is more than 1 distinct product bought, and FALSE 0 otherwise.

So for each patron (in your current selection) this will return -1 if he/she bought more than one unique product (in your current selection), and 0 if not.

summing that will give you the number of patrons which bought more than one unique product, but as a negative number.

So the preceding minus is to get the correct sign.

Re: Conditional Count

Thu, 16 Apr 2015 07:01:13 GMT

Hi Chay,

try this it will work

Expression:

Count(if(Aggr(Count(PRODUCT_ID),PATRON_ID)>=2,PATRON_ID))

Thanks,
Ashok.

Re: Conditional Count

Thu, 16 Apr 2015 07:16:46 GMT

I THINK YOU SHOULD USE ADVANCED SET ANALYSIS, TRY THIS:

=Count({<PATRON_ID= {"=Count(PRODUCT_ID) > 1"}>} DISTINCT PATRON_ID)

Re: Conditional Count

gandalfgray — Thu, 16 Apr 2015 07:19:51 GMT

Using my original stmt will give one too much in some situations because it also counts products not connected (bought) by a patron.

This change will fix that:

-sum( aggr(count(DISTINCT PRODUCT_ID), PATRON_ID)>1 and PATRON_ID)

Ashoks suggestion also works if you add DISTINCT.

Count(if(Aggr(Count(DISTINCT PRODUCT_ID),PATRON_ID)>=2,PATRON_ID))

It is a good practice to try to avoid "if" in chart expressions, but in a case like this it may be easier to understand.

anyway both statements above give the same answers

/gg

Re: Conditional Count

Thu, 16 Apr 2015 07:26:17 GMT

AND IF YOU WANT TO CHECK THE COUNT OF PRODUCT DISTINCTLY TRY THIS:

=Count({<PATRON_ID= {"=Count(DISTINCT PRODUCT_ID) > 1"}>} DISTINCT PATRON_ID)

Re: Conditional Count

Thu, 16 Apr 2015 07:48:50 GMT

Many thanks for the heads up! Will read up on the "aggr" function and learn how it works!

Re: Conditional Count

jagan — Thu, 16 Apr 2015 07:58:35 GMT

Hi,

Try this

Sum(Aggr(if(count (PRODUCT_ID)>1, 1), PATRON_ID))

Regards,

Jagan.