topic Re: Export Excel : issue with null values in App Development

Export Excel : issue with null values

heij1421 — Tue, 14 Feb 2023 10:49:22 GMT

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?

Re: Export Excel : issue with null values

Or — Tue, 14 Feb 2023 11:09:48 GMT

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:

Re: Export Excel : issue with null values

heij1421 — Tue, 14 Feb 2023 11:14:48 GMT

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.

Re: Export Excel : issue with null values

Or — Tue, 14 Feb 2023 11:22:47 GMT

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

Re: Export Excel : issue with null values

marcus_sommer — Tue, 14 Feb 2023 12:08:34 GMT

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.

Re: Export Excel : issue with null values

ROHART — Mon, 24 Mar 2025 13:52:37 GMT

Any update on this in Qlik Sense Nov 2024?