4 Replies Latest reply: Jan 24, 2013 11:39 AM by wendy clegg RSS

    Novice Query - How can I use a distinct count where something equals an 'IF' value

      Apologies I am a novice / self taught user and could do with some help please.  I have limited knowledge so if you could spell it out for me that would be very helpful.




      Sales Person A sells a policy to Mrs X.  Her Premium value is £1000 (IntroNP field)..  However because the client was introduced to Sales Person A by Sales Person B, they split the Introducer commission.  75% goes to Sales Person A and 25% goes to Sales person B.


      The table below shows who should get what. 


      Sales Person


      Introducer Commission %

      Policy Case Count

      Introducer Commission

      Person A





      Person B






      Working bit.


      These are in my expressions bit.


      I have managed to create a bit of working script which allocates the premium (Intro_NP) ONLY to the person who has an IntroducerCommissionPercentage value of 75% or higher


      This script below works  - in this case Sales Person A gets the credit for the premium £1000 and sales person B gets nothing £0. (see Intro_NP in table above)






      I want to do the same with the Policy Case Count –  like the Intro_NP I want to only allocate the case count the sales person who has an IntroducerCommissionPercentage of 75% or higher


      Our current policy case count script is below


      =count(distinct if(description='New Business' or description='Renewal',PolicyId))


      This script works by itself but i want the distinct count to return a value only where it satisfies the IF Introducer commission value >75.  So it looks like the example table above.



      FYI “Description” is the type of transaction and the “PolicyID” is the reference number of the policy.  This script currently allocates the count of 1 to both sales person A and sales person B.  Only sales person A should get this as a policy count.



      I am not sure how to combine an  IF statement and a distrinct count– please can you help.


      Many Many Thanks