Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
nikita23
New 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
New Contributor III

Re: Exporting blank values to excel! #VALUE! error.

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.

4 Replies
MVP & Luminary
MVP & Luminary

Re: Exporting blank values to excel! #VALUE! error.

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

Community Manager
Community Manager

Re: Exporting blank values to excel! #VALUE! error.

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

Sue Macaluso
nikita23
New Contributor III

Re: Exporting blank values to excel! #VALUE! error.

Hi Sue Macaluso,

 

I am using QlikSense.

nikita23
New Contributor III

Re: Exporting blank values to excel! #VALUE! error.

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.