Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data with SALESPEOPLE, CLIENTS, REVENUE.
I want to create an aggregated column where next to each client I have the total number of SALESPEOPLE any given CLIENT has REVENUE greater than zero.
I'm able to get the aggregation correct, but the count function sometimes includes SALESPEOPLE with zero REVENUE.
I tried the expression below, but it's not working to the extent that some zero REVENUE SALESPEOPLE are included in the count.
Maybe there is a way to put a sum() stmt within the COUNT? Any advice much appreciated !!!
=if(aggr(sum({<Year={'2011'}>}Sales),Salesperson,Client)>0,
aggr(count({<Year={'2011'}>} distinct Salesperson,Client))
Hi, try this:
=sum(aggr(if(sum({<Year={'2011'}>}Sales)>0,1,0),Salesperson,Client))
Hope this helps.
Regards