Export Excel : issue with null values

[heij1421]

When exporting a pivot table to excel in Qlik Sense, the empty fields are exported with a '-' symbol.

When adding additional formulas to this excelsheet, like A1+B1 and this A1 field has a '-' value, excel returns #VALUE!.
(sum a1:b1 does work as a workaround). 

Is there a way to change this general dash symbol from '-' to an empty field. Preferable a general setting so I don't have to change all formulas?

[Or · Accepted solution]

As far as I know, there is no way to change the null indicator in the native Qlik pivot. Some extensions (such as Vizlib Pivot Table) do have an option for this.  Depending on the specific situation, you could try replacing the source nulls (assuming that's feasible - in many cases it isn't).

Another potential workaround at the Excel level is to use find/replace to replace the null symbols with a blank:

[heij1421]

When using the 'Find and replace' workaround in excel to replace the '-' with a blank value, I also replace the real negative number from my overview. So -1234 becomes 1234. That's not what I want.

[Or]

That is most certainly not the case. Did you perchance not check the "Match entire cell contents" option?

[marcus_sommer]

IMO is using operators like '+' or '-' quite unsuitable within the most scenarios. Not only in Excel else in Qlik and other tools. Therefore using sum() or similar functions is rather best practice then a workaround.

[nirefraty123]

Easy, check out this:

https://logiexport.logsys.co.il/articles/how-to-easily-control-the-values-formatting