topic Re: Transform null values into zero in App Development

Transform null values into zero

Dat — Thu, 13 Jul 2023 07:47:51 GMT

Hi all,

Any clue on how I could transform null values ('-') into 0, without adding extra rows to my data ?

Re: Transform null values into zero

Chirantha — Thu, 13 Jul 2023 07:50:51 GMT

Hey,

Perhaps using ''NullAsValue'' in the load script could help as discussed here

Re: Transform null values into zero

udit_k — Thu, 13 Jul 2023 08:12:33 GMT

use ALT Function to your chart expression

Example:-

alt(sum(aggr(sum({<[Export Date]={"=len([Export Date])>0"}>} 1),Item)),0)

Re: Transform null values into zero

Dat — Thu, 13 Jul 2023 08:36:45 GMT

Hi @Chirantha ,

It's not working.
I also tried smtg with "if(isnull())", but it didn't work either

Re: Transform null values into zero

Or — Thu, 13 Jul 2023 08:41:14 GMT

Unfortunately, there is no way to do this. Neither NullAsValue nor alt() will help with missing intersections in a pivot table.

Some extensions, such as Vizlib Pivot Table, let you define a custom null symbol which will override missing intersections, but in the native object there is no option of this nature available as far as I know.

Re: Transform null values into zero

Dat — Thu, 13 Jul 2023 08:43:18 GMT

Hi @udit_k ,

in your exemple "Export Date", is your dimension right?

I tried, and it did not work.

"ID" is my dimension, and "Total CA", the field that i want to sum.

Re: Transform null values into zero

Dat — Thu, 13 Jul 2023 09:25:39 GMT

Hi @Or ,

Can I at least show the dimension values when the measure value is null in a straight table ?

In the screen below, the ID 321 does not appear in the straight table for example.

Re: Transform null values into zero

Or — Thu, 13 Jul 2023 09:29:04 GMT

You would need to check "Include zero values" under Add-ons for this.