IsNull not working if ID not in a table

[bdroesch]

Hi,

I have a table with a list of IDs and their respective U.S. state. I have another table that has a list of IDs and the date of last contact. But the second table doesn't contain every ID that is in the first table.

ID	State
1	NY
2	NY
3	MA
4	NJ
5	NJ
6	NY
7	NY

ID	Last Contact Date
1	1/1/2018
2	5/1/2018
3	-

I want to create a pivot table that outputs whether I have a Last Contact Date for all IDs (so all 7).  So I created the dimension listed below and did Count(ID) as my measure.

IF(IsNull([Last Contact Date]) ,'N','Y')

I expected to get 2 for 'Y' and 5 for 'N'.  Instead I got the following:

Flag	Count( ID)
Y	2
N	1
-	4

For the 4 loans that do not exist in the second table, why doesn't the IsNull function tag them as 'N'?

Thank you,

Brian

[ogautier62]

Hi,

it seems to be specific to Qliksense,

here some other formulas maybe could work :

regards