Quick Aggr Question= Count number of customers with sales of product

[bigmike7]

Trying to figure out how to show the number of distinct customers that have sales in a given period of time

I think it would be something like to following but it this is returning a 1 instead of a count:

=

aggr(if(sum({$<$MONTH_INDEX={">=$(=vTimeCEnd-11)<=$(=vTimeCEnd-0)"},PRODUCT_GROUP={'KeyProduct'}>} Sales)>=1,1,0), CUSTOMER_ID)

[sunny_talwar · Accepted solution]

May be you need to add Sum()

Sum({$<$MONTH_INDEX={">=$(=vTimeCEnd-11)<=$(=vTimeCEnd-0)"},PRODUCT_GROUP={'KeyProduct'}>}

aggr(if(sum({$<$MONTH_INDEX={">=$(=vTimeCEnd-11)<=$(=vTimeCEnd-0)"},PRODUCT_GROUP={'KeyProduct'}>} Sales)>=1,1,0), CUSTOMER_ID)

)

[daveamz]

Hi Michael,

Are you trying to use that expression as a dimension? If not, you don't really need the Aggr()

Try this in a text box:

=Count(DISTINCT {<CUSTOMER_ID={"=Sum({<MONTH_INDEX={"">=$(=vTimeCEnd-11)<=$(=vTimeCEnd-0)""}, PRODUCT_GROUP={'KeyProduct'}>} Sales)>0"}>} CUSTOMER_ID)

Please be aware that this nested set analysis can be very slow depending on the data set.

Regards,

David