11 Replies Latest reply: Jan 6, 2012 12:24 PM by tylerwaterfall

Number format setting 'Expression Default' and Excel export

Sander Goudsblom Jun 28, 2011 10:30 AM

Hello all, in a function in a QV application is use the number format 'expression default' in the expression is set the different formats on base of the dimension elements and the num function. This looks like this.

If          ( DimX='1',
          Num(
          $(vFunc1),
          '#.##0,0%')
          ,
If          ( DimX='2',
          Num(
          $(vFunc2)
          ,
          '#.##0,0%')
          ,
If      (DimX='3',
          Num(
          $(vFunc3)
          )
          , 
          '#.##0')
)))

When I use the excel export, it gives the figures as a text field and it rounds the figures. When u use the other formats, other than 'expression default' it exports good. It is also going wrong when exporting from the web front-end (IE-Plugin, ajax not tested), then it's exporting the figure without any format

Excel export from desktop application:

the first two as text and the last one is good

Excel export from web front-end:

the first two without any format and the last one good.

Has anyone seen and solved this before? Can you help me please, thanx in advance!

Number format setting 'Expression Default' and Excel export

Sander Goudsblom Jun 29, 2011 2:11 AM (in response to Sander Goudsblom )

Anyone an idea about this problem?
Like Show 0 Likes (0)

Actions
- Number format setting 'Expression Default' and Excel export
  
  lavvyvkt Jun 29, 2011 2:19 AM (in response to Sander Goudsblom )
  
  Hi,
  Could you please attach the File with some sample data.
  
  Thanks,
  Lavanya
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Number format setting 'Expression Default' and Excel export
    
    Sander Goudsblom Jun 29, 2011 2:41 AM (in response to lavvyvkt)
    
    An example!
    
    Format.qvw 148.5 K
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Number format setting 'Expression Default' and Excel export
      
      Sander Goudsblom Jun 29, 2011 2:11 PM (in response to Sander Goudsblom )
      
      anyone any idea?
      
      Like Show 0 Likes (0)
      
      Actions
      - Number format setting 'Expression Default' and Excel export
        
        Sander Goudsblom Jun 29, 2011 2:13 PM (in response to Sander Goudsblom )
        
        See also this one http://community.qlik.com/message/120429#120429
        
        Like Show 0 Likes (0)
        
        Actions
Re: Number format setting 'Expression Default' and Excel export

Dan Heller Sep 28, 2011 1:38 PM (in response to Sander Goudsblom )

I am also having the same issue as we recently upgraded to QV10 SR3. My table exported as expected in QV9 SR7. So, there is definitely something that changed between the two versions.
Like Show 0 Likes (0)

Actions
- Re: Number format setting 'Expression Default' and Excel export
  
  Peter Turner Nov 18, 2011 5:21 AM (in response to Dan Heller)
  
  Hello,
  
  It sounds like you've found one of the changes between v9 and v10, thedefault excel format is now Text.
  To work around this you can wrap your numeric expressions/fields in the num(), they will then export to excel as numeric values.
  
  From the V10 SR3 release notes:
  The default export format when exporting to Excel is now always text. Ifyou
  experience that exports from your application has changed, then you need tochange
  the format of those columns to some appropriate numeric format.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Number format setting 'Expression Default' and Excel export
    
    Dan Heller Nov 18, 2011 9:41 AM (in response to Peter Turner )
    
    Wrapping the numeric expressions/fields in num() does not work when you have mixed expression number formats and use the "Send to Excel" option. However, it does work when you use the "Copy to Clipboard", ... "Full Table". Then paste it in Excel.
    
    I have created buttons with macros for my end users that do the copy/paste and adjust column/row widths of the cells in Excel where I am using tables with mixed formats. It is one click for the end user and they really like that option.
    
    I was hoping that this was a bug and QlikTech would correct this issue going forward. I am trying to eliminate as many macros as possible, but with this issue, I don't see how to avoid it.
    
    Thanks,
    Dan
    
    Like Show 0 Likes (0)
    
    Actions
    - Number format setting 'Expression Default' and Excel export
      
      Petra Bernehed Nov 18, 2011 12:20 PM (in response to Dan Heller)
      
      Working in 9SR6 and 7 I have found that exporting to Excel depends very much on the version of not only Excel but also of windows. E.g. Exporting "2011/10" (calendar week 10 of year 2011) it is a string not a date function I get differing results:
      
      - W7 Ultimate to Office 2010 - the entire column comes out clean "as is seen in QV"
      - XP to Office 2003 - the column has a mixed status of "as seen in QV" and the number in time format 40nnn. Have not found a way of controlling that.
      - W7 professional to Office 2003 or 2010 often also a mixed salad.
      
      No amount of formatting in Excel sets this correctly.
      
      Maybe these things are related?
      
      Petra
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Number format setting 'Expression Default' and Excel export
        
        Dan Heller Nov 18, 2011 12:24 PM (in response to Petra Bernehed )
        
        There might be a relation, but when we were on QV9 SR7, there were no issues at all with the Excel export. Now that we are on QV10 SR3, this is where we encountered the problem.
        
        Like Show 0 Likes (0)
        
        Actions
    - Re: Number format setting 'Expression Default' and Excel export
      
      tylerwaterfall Jan 6, 2012 12:24 PM (in response to Dan Heller)
      
      Thank you Dan for the information.
      This change in QlikView 10 (exporting as text) has caused a lot of grief at our company as we are transistioning from 8.5 to 10.
      
      Like Show 0 Likes (0)
      
      Actions

Go to original post