Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
heij1421
Partner - Contributor III
Partner - Contributor III

Export Excel : issue with null values

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?

 

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

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:

Or_0-1676372981165.png

 

View solution in original post

5 Replies
Or
MVP
MVP

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:

Or_0-1676372981165.png

 

heij1421
Partner - Contributor III
Partner - Contributor III
Author

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
MVP
MVP

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.