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

       

       

      ANY HELP WOULD BE GREATLY APPRECIATED.