Formatted as text in Excel exports in QlikView 12.50 SR4

[krumbein]

Hello!

We have recently upgraded vom QV 12.30 (latest SR) to QV 12.50 SR 4.
QlikView only, no NPrinting.

Now numbers are formatted as text,  when exporting to Excel. The formatting is as in QlikView though. What I'd expect would be to get numbers in Excel, but formatted as in QlikView. That is how it worked until the upgrade. Even when we transform the text back to numbers in Excel, some precision is lost.

I have found the following related issues:

Numbers with default format exported to XLSX with extra digits.
Jira issue ID: QV-17805, QV-17184, QV-17899
Description: Numbers in objects with no predefined format, were exported as "REAL" number in XLSX, this
caused exported numbers to have an extra digit. This change fixed the issue.
Note: When there is no predefined format, numbers will have exported as "TEXT" in XLSX instead to align the
object layout as in QlikView.
(Solved in 12.50 IR)

Excel export produce the wrong number formatting
Jira issue ID: QV-20560; QV-20652; QV-20673
Description: Incorrect results for Excel export. Zero is sometimes shown as blank cell, and numbers are
sometimes formatted as text.
(Solved in 12.50 SR 2)

REAL number format is lost sometimes when export to Excel
Jira issue ID: QV-21505
QV number format string is different from Excel's. Sometimes they're compatible, sometimes not. When QV
REAL num format cannot be converted to Excel's, the data cell has earlier been exported as a number without
format, which makes it looks differently from QV charts. This change fixes issue by exporting data cell as
"General" format and correct representation
(Solved in 12.50 SR 4)

The first issues gives a hint on a work around: to apply the formatting in the Number tab. That does indeed to formatting in Excel, as we are used to it. But we don't use the formatting in the Numbers tab, as it is much easier to format uniformly by applying formating functions to the expressions themselves (e.g. money(...) or num(...)). In fact we are using money(...) everywhere (you can overwrite the formatting to whatever you want), as that has been more stable regarding the Excel export.

It is not the issue of mixed formatting, that can be solved with the ExcelExportMixedAsText option. 

Do I understand it correctly, that to get the desired result in the export (a correctly formatted NUMBER) we are currently stuck with applying the formatting in the Numbers tab? Or is there any way the formatting QV formatting is not compatible with Excel anymore, causing this behaviour?

Thanks,
Sandro

P.S. Why did they have to fiddle with that anyway? All was fine as far as our installation was concerned 😄

[Or]

You are correct. You either format through the number tab, or you don't get numbers. This also means conditional numeric formatting can't ever be exported as numbers, and ExcelExportMixedAsText no longer works (see comments under https://community.qlik.com/t5/Support-Updates-Blog/QlikView-12-50-SR4-is-now-available/ba-p/1783889 ).

Qlik hasn't announced any plans to change / fix this that I've heard of, and I have no idea why they did it in the first place.

[krumbein]

Thank you for the info! I am a little dumbfounded and not sure what to say...

I am hoping, that someone has information, that I like better, so I'll wait with accepting that as a solution 😉

[Or]

---

@krumbein wrote:

I am hoping, that someone has information, that I like better, so I'll wait with accepting that as a solution 😉

---

You and me both! We had to go over every expression in every object in every QVW and set numeric formats, but we still don't have a fix for dynamic formatting, so if there's any news or advances on this it'd be great.

[krumbein]

but we still don't have a fix for dynamic formatting, so if there's any news or advances on this it'd be great.

The users can change the formatting at "runtime", right? Have you considered using macros to do that? Not that this would diminish the need for the numeric export format to come back as it was...

Regarding setting the numeric format in the Numbers tab (if I really have to do that), I'll probably use my goto solution for tedious QlikView tasks: whipping up a Powershell script to do that. Anyone let me know, in case you are interested

Sandro

[Or]

Not necessarily at runtime... just formatting using conditional statements, for example, when making a P&L style table, some rows need to be formatted as currency and others as percentage for the same column so we'd use conditional formatting. This might also be the case for e.g. quality control where different measurements need to be displayed with different precision levels (some are measured in whole units, while some might require multiple decimal places).