Date Fields Formatted as Text When Exported from QlikView
Article Number: 000060940 | Last Modified: 2018/12/18
In QlikView, there are two main options for creating calculated items in a Chart > Straight Table object. 1 - Acalculated dimension can be added in the Dimensions tab in Chart Properties. 2 - An expression can be added in the Expressions tab of Chart Properties
The Presentation tab only allows format of the alignment (left, center, right) of the 2 types of calculated items. The number tab only allows formatting options for the items created in the Expressions tab. It was reported that these two different options for calculations behave differently when exported to Excel, even when the actual expressions used for both are the same. for the example which was tested, Date format was the goal. The Dimension items (created in the Dimensions tab) show up in excel as TEXT format. The Expression items (created in the Expressions tab) show up in excel with DATE format. This can cause problems with Date calculations performed in the excel file after export from QlikView.
Environment: QlikView Nov 2017 (12.20) QlikView 12.10
This was reported to be working as designed by R&D. Dimensions are usually text, and based on tested in excel these show up with text format after export from QlikView.
An existing bug was found for this issue: QV-11149. This was bug was closed by R&D as working as designed. The workaround is to do the formatting in excel after the export, if additional calculations need to be done in the excel file after exporting from QlikView. An additional workaround is to use the 2nd type of item listed above and create an expression in the expressions tab of the properties sheet. Each expressions number format can be specified in the Number tab and this often shows up as the proper number format (not text) in excel after exporting.
Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.