6 Replies Latest reply: Apr 29, 2016 12:09 PM by Gysbert Wassenaar RSS

    Returning the sum of an expression for the first 10 instances

    Kyle Luckow

      Hello,

       

      I am trying to get this formula to work for the first 10 instances of appointment by our dimension SalesRepName

       

      SUM({<EventType={'Order'}, IsOrderCancelled={0}>} SalePrice)

      / (

      COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={*}>} DISTINCT AppointmentId)

      - COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={'Not Home'}>} DISTINCT AppointmentId)

      - COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={*}, IsSameRepResit={1}>} DISTINCT AppointmentId)

      - COUNT({<EventType={'Appointment'}, AppointmentStatus=-{'Deleted','Measure Call'}, AppointmentResult={*}, IsCancelSave={1}>} DISTINCT AppointmentId)

      )

       

      I am trying to figure out how to use the Min or First formulas to make this work.  Each appointment has a date (CalendarDate) associated with it that I believe could be leveraged. The goal is to be able to get this calculation by sales rep (SalesRepName) for their first 10 appointments (AppointmentId).  I would like to have this in a table with the formula as a column and SalesRepName as the dimension.  Does anyone know how to get that to work?

       

      Thanks!

       

      Kyle