Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
nikita23
Contributor III
Contributor III

Exporting blank values to excel! #VALUE! error.

I have a straight table and pivot that contains null or missing values.  When user want  to exports the tables to excel, sometimes he/she wants to add up selected cells across a row using the arithmetic operator '+' and paste that same formula down the column.  The problem is when one of those cells contain a null value excel returns the #VALUE! error.

 

I've tried replacing the null and missing symbols within the tables with nothing, but when the tables exports to excel it's not equivalent to an empty cell.

(There's an over-simplified example attached, export the chart to excel and try =B2+D2, you'll get #VALUE!, but try =D2+ any cell outside the table and it will actually calculate a value for you)

 

Replacing the null symbol with '0' works, but for aesthetic reasons we don't want a bunch of 0s all over the tables .  I've also tried changing text values to match the background color in the chart (so that the null values represented by '0' are there, but just can't be seen) and I'm not so happy with that just because once it's in excel if someone happens to accidentally overwrite the cell value with a different value you won't see it unless you have the cell selected.

 

Dose anyone have any suggestions?  

Thanks in advance!!

1 Solution

Accepted Solutions
nikita23
Contributor III
Contributor III
Author

Thanks Marcus!!

 

but it has been resolved by using the expressions

if(isnull(Month), '',Month )

because there was a gap in my expression between ' ' like this thats why it was consider this as a character.

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Using the normal operators to perform a calculation is often not the best choice. Better will be your users use:

=sum(B2;D2) instead of =B2+D2

- Marcus

Sue_Macaluso
Community Manager
Community Manager

@nikita23 Are you using QlikView or Sense?  I would like to move this into the correct product forum. Thank you

Sue Macaluso
nikita23
Contributor III
Contributor III
Author

Hi Sue Macaluso,

 

I am using QlikSense.

nikita23
Contributor III
Contributor III
Author

Thanks Marcus!!

 

but it has been resolved by using the expressions

if(isnull(Month), '',Month )

because there was a gap in my expression between ' ' like this thats why it was consider this as a character.

View solution in original post