4 Replies Latest reply: Jun 19, 2013 9:39 AM by Robyn Talley RSS

    Count Null Fields

      I need help with my expressions.  My sample data is attached in the qvw.  I am trying to do two things...


      1.          Count # of Sales Agents who have presented and talked to 3 or more customers.  This needs to be a weekly average shown at the Month level in a  chart.     Each presentation is identified witha unique presentation key.


               Currently my formula looks like this but does not work properly:


                sum(aggr(if(count([Sale Key])>=3 , 1, 0), [Sales Agent],Week))


      2.          Count # of Sales Agents who have 3+ presentations but did not make a sale.  If there is a null in the Sale Key column then this implies the presentation did not result into a sale.


                My incorrect formula:


                sum(aggr(if(count([Sale Key])<=0, 1, 0),  [Sales Agent], Week))