5 Replies Latest reply: Aug 29, 2013 5:19 PM by matthew thompson RSS

    Format error when exporting to Excel


      I have an unexpected issue when I export to Excel the data of a pivot chart. In this object I have a numeric dimension: into Excel this is sended as a text but in QV I can see a number format with thousand separator. In QV I have used num(dim, '#.##0').

      Does anyone have this problem?


        • Format error when exporting to Excel
          Patricio Solari

          What you can do is go to Settings/Document Properties/Number and there, after selecting your field, you can decide what is the format for this field for the whole document. If the format is defined in your chart, this format will not apply to the export if in the document you have something different.

            • Format error when exporting to Excel

              Just want to bump this issue again rather than making a new thread. I have the same problem.


              I have three numbers as dimensions and they get converted to text when exporting to Excel. I have tried setting them to "Fixed to" under the numbers tab in document settings but that formatting is ignored when used in dimensions. I have tried formatting with the num() function which displays correctly in the table when in QV but is still text when exported to Excel. Using QV9 SR6.

                • Format error when exporting to Excel

                  Have you tried copy and paste special rather than just export?  I used to have the reverse problem (part number field with a mix of numeric and alpha-numeric values) where I needed everything to stay as text and some would autoconvert to general and some values would be corrupted - loosing leading zeros, changing to a date format etc.


                  If it doesn't work with just pasting, try formatting the number column as number rather than general in excel and then paste special > values if it will allow that.


                  Not as efficient as just a single click but this looks like one of those "features" in QlikView that isn't important enough to solve!




              • Re: Format error when exporting to Excel

                I can see that this issue is couple of year old already but I just got this myself. Couldn't figure out a work-around and it clearly seems that from pivot table object you can't export numerical values as numbers to excel. In a way very very small issue but one of our client's has otherwise a very useful report and they understandably get annoyed when numbers are not coming as numbers...


                If there is some kind of formatting trick one could do for the chart to get this working, that would be great! (We are using AJAX and QV10)