Pivot Table - Zeros instead of NULL

aPsikus

Hi,

I would like to put 0's instead of NULLs in Pivot table.

As I see IFNULL formula, do not solve the trick.

I have found this webpage with hints, and I have used this format:

#,##0;-#,##0;0

But after export to Excel zero's are TEXT, and to have them as numbers, cells need to be converted manually.

Is there a solution that zeros will be treated as numbers after export?

Joel

The solution you're using converts the values to text. You'll need to convert the NULL to an actual zero. Ideally, this should be done in the source system or ETL but we don't live in an ideal world!

The IFNULL should do what you need.

aPsikus

Hi,

Under mentioned link it is provided as solution for numeric values in pivot.

IFNULL do nothing in case of pivot. There are empty values.

Joel

Using the custom data format converts the field to a text field when it is exported to Excel. Using the IFNULL(measure,0) retains the measure as a number when exported to Excel. Are you by any chance putting the 0 in quotes in the IFNULL expression?

aPsikus

No, standard IFNULL formula: IFNULL(fact, 0)

Gianni Ceresa

I guess the IFNULL isn't working because there isn't a NULL like the OP said in the title.

It's just empty, nothing at all (which is not NULL), so the formula can't be executed on something which doesn't exist and that's why the IFNULL doesn't return anything.

Thomas Dodds

There's no data point at the intersection of your dimensions ... that's valuable in formation AND different from a 0 (zero) at that intersection.  Don't muddy the intelligence displayed.  Technically you are changing data at run-time -- HUGE risk.

aPsikus

How can I confirm that?

User has required to put 0's in empty fields, so I have assumes that it is null.

Thomas Dodds

In your data source you should be able to find occurrences where a dimensional key is not represented in the set of intersections of the other keys.  Simple profiling queries are all that's needed.