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

Wed, 23 Jan 2013 10:50:17 GMT

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

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

Thu, 24 Jan 2013 05:14:22 GMT

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.

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

jagan — Thu, 24 Jan 2013 06:07:51 GMT

Hi,

Try this

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

Regards,

Jagan.

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

Thu, 24 Jan 2013 16:33:21 GMT

Jagan - thank you that works perfectly - much obliged.

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

Thu, 24 Jan 2013 16:39:47 GMT

Many Thanks Khaled

topic Re: Novice Query - How can I use a distinct count where something equals an 'IF' value in QlikView

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

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

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

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

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