Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
)
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)
)
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