The issue can be worked around differently depending on QlikView version.
QlikView 10 SR2 to 11 SR1
The only way to resolve this in QlikView 10 is to reformat the values in Excel or format the chart column to one format instead of the mixed formats.
QlikView 11 SR2 or later
Numerical export to Excel can still be forced by an an Easter Egg setting in QlikView. The setting is called ExcelExportMixedAsText and defines if mixed format data should be exported as text or numerical value.
The default value for ExcelExportMixedAsText is 1 and means that export of mixed data will be done as text. If the value is set to 0, mixed fields will be exported as numerical values.
Keep in mind that when ExcelExportMixedAsText is 0 the numerical values will be exported based on the stored or calculated result, and not on the currently presented format in QlikView. This can be see in the example above where the 5% value is exported with the underlying numerical value of 0.05 and the two decimal value 800000.80 is exported with the underlying numerical value of 800000.8.
Enable Easter Egg feature in QlikView Desktop client
- Open QlikView Desktop Client
- Open the QVW file that requires the change
- Go to Help > About QlikView…
- Right click on the QlikView logo in the lower left corner of the about dialog
- Highlight ExcelExportMixedAsText in the settings list
- Enter the desired value to export mixed values as text (1) or numbers (0)
- Click Set to store the value
- Click Save Next Script on Load
- Click Close to close the settings dialog
- The limit has now been changed in the QlikView client, and will affect all QlikView applications opened in the client
Enable Easter Egg on QlikView Server
- Change the QVW level setting as described in above section
- Save or publish the QVW to the QlikView Server Documents folder
- Open the QlikView Server’s settings.ini file in a text editor. Default file path; C:\ProgramData\QlikTech\QlikViewServer\Settings.ini
- Add ExcelExportMixedAsText =nnn in the [Settings 7] section of the settings file. Note that nnn should be replaced with the same value as specified in step 6 in above section
- Save the settings file and close text editor
- Restart QlikView Server, to trigger loading of the altered settings
- Access the QVW from QlikView web client to see that the behavior has been changed for all QlikView applications accessed through the server
The Easter Egg features are not supported and should be used with caution. The features may be withdrawn in later service or major releases of QlikView.