Problem with Distinct Count function

[#1]

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

[Oleg_Troyansky · Accepted solution]

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

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

[#3]

Thanks Oleg, that worked for me.