Conditional Count

[#1]

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!

[#2]

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

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

[gandalfgray · Accepted solution]

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

[#4]

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

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

[#5]

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

[jagan]

Hi,

Try this

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

Regards,

Jagan.