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.

       

      Scenario

       

      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

      Intro_NP

      Introducer Commission %

      Policy Case Count

      Introducer Commission

      Person A

      £1000

      75%

      1

      £37.50

      Person B

      £0

      25%

      0

      £12.50

       

      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)

       

      SUM(if(IntroducerCommissionPercentage>75,Intro_NP,0))

       

      Problem

       

      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