Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
terrykavouras
New Contributor II

Zeroes (0's) export as text in Excel

I've been wrestling with the following problem all morning:

I have a pivot table with all sorts of numbers.  Some of them zeroes.  When I export to Excel from AccessPoint, most (but not all) zeroes are exported as text (see attached image).  I've hunted through this and other sites for a couple hours now and haven't found the answer.  I've tried the following:

  1. Multiply the expression by 1
  2. Use the Num() function around the expression
  3. Use an If() statement to force a zero value (something like: If(IsNull(Sum(STDAMT)) or sum(STDAMT)=0, 0, Sum(STDAMT))
  4. Setting the ExcelExportMixedAsText property to 0

I'm stuck. Even more frustrating is that some of the zeroes come through as numbers in the Excel export.  Explain that one!

Here is the pivot table in AccessPoint.  There are some zero values as you can see:

PivotTable1.png

Here is that same pivot table exported to Excel.  Most, but not all, of the zeroes exported as text.

PivotTableInExcel.png

What's the deal here?  Is this a bug?  Any ideas that I haven't tried?  To be clear, I want zeroes to come into Excel as numbers, not text. Any solutions or workarounds?

9 Replies
olivierrobin
Valued Contributor III

Re: Zeroes (0's) export as text in Excel

hello

try to put your field in numeric format  (number, integer, ...) in Number Tab

Frank_Hartmann
Honored Contributor II

Re: Zeroes (0's) export as text in Excel

please prepare a small sample qvw with scrambled data in which you show your problem.

this helps us to investigate the problem and recommend possible solutions.

Preparing examples for Upload - Reduction and Data Scrambling

this might be helpful as well:

https://community.qlik.com/thread/227022

https://community.qlik.com/thread/216606

terrykavouras
New Contributor II

Re: Zeroes (0's) export as text in Excel

Thanks, the number format (Fixed) for the expression has already been set.  This does not fix the zero as text problem.

MVP & Luminary
MVP & Luminary

Re: Zeroes (0's) export as text in Excel

Quite probably are your 0 which aren't exported as numbers NULL values which are replaced with the string-value of '0' in tab presentation. To display a real 0 you could use an expression like: alt(sum(STDAMT), 0) and disabling the option of hiding NULL also within the tab presentation.

- Marcus

robin_hausdoerfer
Valued Contributor III

Re: Zeroes (0's) export as text in Excel

perhaps there are missing values?!?

terrykavouras
New Contributor II

Re: Zeroes (0's) export as text in Excel

Thanks for the idea, I tried Alt(Sum(STDAMT)) but, unfortunately that didn't work either.

terrykavouras
New Contributor II

Re: Zeroes (0's) export as text in Excel

Also, interestingly, if I export to Excel from the QlikView client, the zeroes are treated as numbers, not text.  The problem only seems to arise when I export from the Ajax client.

terrykavouras
New Contributor II

Re: Zeroes (0's) export as text in Excel

Thanks for the advice.  I've attached a scrambled version of the app.  The problem only happens from the Ajax client.  The export to Excel treats the zeros as numbers when done from the QlikView client. But when you export from Ajax most of the zeroes are converted to or treated as text.

MVP & Luminary
MVP & Luminary

Re: Zeroes (0's) export as text in Excel

My suggestion was a bit different: alt(sum(STDAMT), 0) and disabling the hiding of NULL within tab presentation - for me it worked with the AAX client.

- Marcus