How to count Null values for multiple fields

[Lucky1]

Hi Team

I need to count null values present in all 3 fields.I have a data like below. having nulls values in all GBT and JV and CWT fields. I want to count nulls in all 3 fields.In below case i should get output as 1 in my kpi as GBt and JV and CWT have nulls in id 4

I have used below query in kpi but didnt worked

Count(If(Len(Trim([TR.GBT_DATA_UNIQUE_ID]))=0 and Len(Trim([TR.CWT_DATA_UNIQUE_ID]))=0 and Len(Trim([TR.CWT_JV_DATA_UNIQUE_ID]))=0, SID))

[Aditya_Chitale]

With provided data from screenshot, I am able to get correct result.

Make sure you using the same ID field associated with other table fields used in calculation. If that doesn't work, please share sample data by jumbling numbers.

Regards,

Aditya

[pcv_devo]

Hi @Lucky1 ,

First of all, I am assuming that the COUNT that you dictate here differs from the one in your application. You should count IDs, not SIDs.

Assuming this, make sure you are having the record ([ID],Null) in your data model, like this (and like the excel):

Under these premises, the count works perfectly. If it keeps giving you an error, you should see the data model. The KPI function will look for the fields in the existing tables in the model, so if you have these fields repeated or are join tables, it may be that you are mixing information.

I hope it helps you.

[Or]

Sum(if(RangeSum(Gbt,JV,CWT)=0,1))

Assuming the values are all numeric and there are no blanks (just nulls), that's the cleanest way insofar as ease of reading.

You could also write this as a set by counting IDs, I think:

Count({$ - {< Gbt = > + <JV = > + < CWT = >} }Distinct ID)

But this is kinda messy to read.