Null and data completeness percentage

[deepakkrish]

Hi Everyone , 

I have two tables Table A and Table B , my requirement is I have to calculate How many null values Table A and Table B  have and Data completeness percentage for both the tables .

below is my tables,

Table A				Table B
name	address	phone		salary	contact	pin
A	null	789		null	null	123456
b	null	null		null	null	456
c	BG	null		78909	null	789

expected output

Tables	null	completeness
Table A	4	55%
Table B	5	44%

Below is my expression :

Null :
if($Table='Table A', sum(if(IsNull(name),1,0))+sum(if(IsNull(Address),1,0))+sum(if(IsNull(phone),1,0)))

completeness:
if($Table='Table A', (1-sum(if(IsNull(name),1,0))+sum(if(IsNull(Address),1,0))+sum(if(IsNull(phone),1,0)) /
sum(if(not IsNull(name),1,0))+sum(if(not IsNull(Address),1,0))+sum(if(not IsNull(phone),1,0))))*100

I got null values correctly , please help me to get completeness by percentage.. 

Thanks,

Krish.

[StarinieriG]

Hi

try with

1- (your null expression / ($Rows*Max($FieldNo)))

[deepakkrish]

Thanks for your reply .. this is not working

[asinha1991]

there might be a better way but you can do this

(count(name)+count(Address)+count(phone))/NoOfRows('TableA')*3

3 is number of fields which you already know..

also for counting null there is a direct function NullCount(FieldName) you don't need to use sum with if

[Brett_Bleess]

Krish, did the last post help you get things working?  If so, do not forget to use the Accept as Solution button on the post to give credit for the help and let others know that worked.  If you are still needing help, leave an update for us.

Regards,
Brett