Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

exporting from qlikview to excel the date format to be mmm-yy

Hi,

When I export data to excel the date format is not kept the same as in the Qlikview model. I have my SET DateFormat='MMM-YY';, but when it exports to excel it exports as "dd-mmm" format.

However, if I export for a few months which is less data it exports fine, but once i export for more months which is more data (more records), it exports as "dd-mmm" format !

Anyway around this to be exported in the same format as the model which is "MMM-YY"??

Regards, Jivesh

11 Replies
stigchel
Partner - Master
Partner - Master

Have a look at this thread, last comment maybe a workaround..

Export to Excel issue

HirisH_V7
Master
Master

Hey,

Try this for your date field,

Date(Date#(DateField),'MMM-YY')


Wrong date format when export to Excel


HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Not applicable
Author

Hi Hirish

This is my scrip below, where would I place your recommendation scrip?

I have also did screen shots of the Model, the date format in the Model is correct, but when I export the data to excel, csv, it exports in the date format as dd-mmm. This only happens when I export for many records of data. I have read the "Wrong date format when export to Excel" stream no soluton cn be derived from this one so as the one from stigchel.

FOR EACH vSheet IN '[Secondary FY14]','[Secondary FY15]','[Secondary FY16]','[Secondary FY17]','[Primary FY14]','[Primary FY15]','[Primary FY16]','[Primary FY17]'

Primary_Secondary:

LOAD Secondary_Primary,

     Brand,

     [Vitabiotics Brands],

     Product_SKU,

     [SSD Suppliers_Primary Wholesalers],

     [SSD Customer Name_Primary Ship-to],

     MedPages_Code,

     Year,

     MONTH,

     MAT,

     TEAM,

     [Courier_Non COURIER],

     [RX Territory],

     [Self Care Territory],

     Region,

     [RX RSM Region],

     [Self Care RSM Region],

     Value,

     [SSD Adj Value (SEP)],

     Units

FROM

(ooxml, embedded labels, table is $(vSheet));

Qlikview Model with Month.JPGExport data.JPG

HirisH_V7
Master
Master

Hi,

check like this using,

Date(Date#(Month,'MMM-YY'),'MMM-YY') as Month

At Month field in your script.

HTH,

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
stigchel
Partner - Master
Partner - Master

I'm starting to think this is related to your windows regional settings for date, can you check?

Not applicable
Author

Hi Hirish

When I use this scrip, my Months disappear?

Not applicable
Author

Hi Piet,

My regional settings are set correctly.

HirisH_V7
Master
Master

Try exporting only month list-box to excel.

HirisH
“Aspire to Inspire before we Expire!”
jonathandienst
Partner - Champion III
Partner - Champion III

You have two options:

  • Export the field as text - this will allow you to control the format, unless Excel recognizes the date format and   converts it to a date, in which case Excel controls the format
  • Export the field as a date - Excel will control the format
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein