NULL in numeric field is treated like a positive number close to zero

[magdaho]

Hi Fabien, first of all let me thank you for such a great plugin as your Enhanced/Document table and that you are still developing it even for new Kibana versions!

I need your advice in this case. It seems that NULL value (empty) in numeric field (type float) is treated by Enh/Doc table like a positive number very close to zero. I tested two different versions of Enh/Doc table plugins in Kibana 7.9.1 and 7.17.0 (Enh/Doc table plugin v1.13.0 was the latest one).

Column Number is the original float field. Column IsNull is computed column with formula:
col['number'] == 0? "zero" : col['number'] < 0 ? "negative number" : col['number'] < 0.001 ? "Is NULL" : "positive number" To identify the NULL value I used condition < 0.001 or any similar number close to zero. It causes problem e.g. in Total Average, it influences the result, as NULL is calculated as "almost zero".

Can you please think of any workaround?

Thank you, Magda

[fbaligand]

Well, to check that col value is null, just test col[´number']. It tests if column value is defined.

[magdaho]

Actually the issue is not to check if a value is defined. The issue is that when a value is null (is not defined), it is treated like number 0 (or very close to zero). Instead of ignoring the empty fields in calculations, it takes them as zero. Thus when I calculate Total Average per the column, it shows incorrect Total number .

[fbaligand]

Well, globally, you should not do computing with null values. you can use this function to be sure to have 0 value: col(‘number', 0)

[magdaho]

But 0 instead of null would change the meaning of the value, it would not be true. So I cannot change it to 0.

The first line value is null. Second line value is 0.833. As the first line value is treated like 0, it calculates the average = 0..417 Is there a way how to get correct Average, i.e. 0.833?

[fbaligand]

Well, sorry, there is no way to do that. Average is done on every line. There is no way to say « average only on non null values »

[fbaligand]

By the way, classic data table (provided by kibana core) process null values as 0 values.

[magdaho]

I understand. Thank you for your responses!