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

[#1]

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

[#2]

Hi,

Perhaps, you can use something like this as your expression:

=count({<description={'New Business'}+{'Renewal'},IntroducerCommission={'>75'}>}distinct PolicyId)

Hope it helps.

Regards,

-Khaled.

[jagan · Accepted solution]

Hi,

Try this

=count({<description={'New Business', 'Renewal'},IntroducerCommission={'>75'}>}distinct PolicyId)

Regards,

Jagan.

[#4]

Jagan - thank you that works perfectly - much obliged.

[#5]

Many Thanks Khaled