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!

[amit_saini]

Try,

Similar below:

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

Thanks,
AS

[amit_saini]

Also:

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

Thanks,
AS

[amit_saini]

Also:

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

Thanks,
AS

[#5]

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

[stabben23]

Hi,

is this what you are looking for?

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

[#7]

Hi

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

[gandalfgray]

Hi Chay

Try this:

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

hth/gg

[gandalfgray]

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.

[#10]

Hi Chay,

try this it will work

Expression:

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

Thanks,
Ashok.