Problem with Distinct Count function

Sat, 01 May 2010 08:03:43 GMT

Folks,

I have a simple problem, but having spent hours lookign at it I can't seem to fix it but I am sure it is something obvious.

I have some staff data, containing First_Name, Last_Name, Assignment_Number, VIR_Date, Hire_Date, VIR_Number

I have shown a sample below, I am seeking to categorise staff based on a count of VIRs based on the Length of Service (VIR_Date - Hire_Date)

ASSIGNMENT_NUMBER

FIRST_NAME

LAST_NAME

Hire Date

VIR_Date

VIR_Number

4508976

Jim

Smith

31/05/2005

30/03/2010

HT090674

4508976

Jim

Smith

31/05/2005

08/04/2010

HT090710

4508976

Jim

Smith

31/05/2005

21/04/2010

HT090721

My formala for the example is below, essentially I am trying to count the number of unique VIRs where the Length of Service (V.I.R_Date - Hire Date) is less than 2,which should equal ZERO but for some reason the formula keeps giving me 1 :

count(distinct if ( ((Date-[Hire Date])/365)<=2,[V.I.R. Number],0)))

Thanks in advance for your help

Problem with Distinct Count function

Oleg_Troyansky — Sat, 01 May 2010 10:49:11 GMT

Your if() returns 0, and count(distinct 0) = 1.

Replace "0" by null() and it will work as expected.

Problem with Distinct Count function

Sat, 01 May 2010 20:51:07 GMT

Thanks Oleg, that worked for me.

topic Problem with Distinct Count function in QlikView

Problem with Distinct Count function

Problem with Distinct Count function

Problem with Distinct Count function