Zeroes (0's) export as text in Excel

[terrykavouras]

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:

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

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?

[olivierrobin]

hello

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

[Frank_Hartmann]

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]

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

[marcus_sommer]

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

[Anonymous]

perhaps there are missing values?!?

[terrykavouras]

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

[terrykavouras]

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]

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.

[marcus_sommer]

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