1 Reply Latest reply: Mar 30, 2011 2:21 PM by ivan_cruz RSS

    COUNT of ONLY NON-NULLs and NON ZERO Sales

    Henry Noh

       

       

      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))