I am trying to get a count of all candidates who passed manager interview but do not have a grade , problem is, the grade column has both nulls and '', so if I use the code below, I am only getting a count where the grade column has a null and but, my question is how can I modify this code to capture both nulls and ''.
FILTER("Fact - # of Applicaitons" USING (IFNULL((case
when "XX"."Job Information"."Job Family Name"='Claims' then "XX"."Application Grade Details"."Final Claims Grade"
when "XX"."Job Information"."Job Family Name"='Soup' then "XX"."Application Grade Details"."Final Soup Grade"
when "XX"."Job Information"."Job Family Name"='Key' then "XX"."Application Grade Details"."Final key Grade"
when "XX"."Job Information"."Job Family Name"='Damage' then "XX"."Application Grade Details"."Final damage Grade"
End), 'Missing Scores') ='Missing Scores' AND "Application Grade Details"."Manager Decision"='Pass'))
Honestly? Best advice is to sort out the data quality issue in the source. You are trying to analyze things...not correct mistakes and inconsistencies! Every correction slows doen the analytical system. And things become especially problematic if you do things like shown above in the front end. Not only does the "correction logic" execute a thousand times per day fot each access rather than being corrected once and for all in the source, the logic itself will also need to be multiplied, reproduced and maintained for each point of usage. Long story short: sorry to say this but already conceptually and approach-wise this is the worst way of handling the issue.