count of customer where sum sales > 0

[kelsonqlik]

Hi Community,

I'm working in a chart and I'm trying to get the count of "customer_name" that have "customer status" =  "New Logo" for a given time period, say fiscal year, where New ACV(sales) is greater than 0. So the ACV greater than 0 is at a line item level related to individual customers at specific times.

The calculation for New ACV is a set expression:

Sum({<[LOB Description]={'Support','Subscription Software','Cloud Services'},ACV_CALC_EXCL_FLAG={'N'},[Booked Flag]={'B'},DVP={'x', 'y', 'z'},[Deal Driver]={'Upsell','New','Renewal','Cross Sell','New Conversion'},[Renewal Flag]={'N'}>} [ACV Plan Rate Amount])

I've tried a bunch of different things including calculations with count, distinct, aggr, if, etc. but I'm struggling to get what I need. The set expression seems to be adding more complexity too.

This is an example of what the data looks like (where New ACV is calculated by expression above):

and this is the result I need:

Any thoughts on how to resolve this? Really appreciate any help!

[124rooski · Accepted solution]

I think you want to use count as the aggregator instead of sum. Given your condition listed out - the count for customers where customer_status = 'new logo' and new AVC > 0 would look something like this:

count({< [New ACV] = {">0"}, [Customer Status] = {'new logo'} >} Customer)

Hope this helps.

[kelsonqlik]

Hi @124rooski , thanks so much for your reply. This worked but i had to add distinct after count like:

count(distinct{< [New ACV] = {">0"}, [Customer Status] = {'new logo'} >} Customer) to get the right numbers I was looking for.

A coworker of mine also gave me:

count(distinct{< [New ACV] ={"$(='>' & (0))"}, [Customer Status] = {'new logo'} >} Customer)

which also seemed to work and give me the right numbers. Have you seen ={"$(='>' & (0))"} expression before? I'm a bit confused on the syntax. and the meaning of "$" before.

[MayilVahanan]

Hi @kelsonqlik 

{"$(='>' & (0))"} <-- its also give '>0' as result
$() <-- evaluates the expression.

For your scenario, you can write like 

count(distinct{< [New ACV] = {">0"}, [Customer Status] = {'new logo'} >} Customer)
-- no need to confuse 

[kelsonqlik]

thank you @MayilVahanan for the clarification! very helpful