This content has been marked as final. Show 4 replies
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.
Introducer Commission %
Policy Case Count
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