Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
pokassov
Valued Contributor

Calculated 'date' dimension exported to excel as number

Hi all,

 

A have a straight table where one of the dimensions is =v_Date, where v_Date is 11/10/2018

QV treats it as a date and I'm happy with it. 

But when I try to export the table to the excel, it shows value as a number.

I tried:

=Date(v_Date)

=Date('$(v_Date)')

each time QV shows me the correct date but when I export to excel - the same results - number and "General" as a format of the dimension.

I explicitly corrected it to text:  =Text(v_Date) and changed Presentation >> Alignment >> Data(Text) to left. At least it still looks as "date" in QV and looks similar in Excel.

Although the format of the dimension in excel of course General.

 

How can I export a table with calculated dimension to excel without losing Date format?

 

Cheers,

Sergey

Labels (5)
7 Replies
Partner
Partner

Re: Calculated 'date' dimension exported to excel as number

Hi,

maybe try specifying the format in QV.

Date(v_Date,'MM/DD/YYYY') - or whatever your format is.

could you attach an example QVW if it doesn't work?

 

pokassov
Valued Contributor

Re: Calculated 'date' dimension exported to excel as number

Hi!

Please find an example.

If you export the table to excel you will see the difference between the fields.

Even if I apply date() to a field that already is date - the excel results will be different.

 

Cheers!

Partner
Partner

Re: Calculated 'date' dimension exported to excel as number

It could be excel settings as i have exported the table in the QVW to excel (right click>Send to Excel) and the output is correct..

Capture.JPG

Partner
Partner

Re: Calculated 'date' dimension exported to excel as number

Column A has exported with the cell formats of 'Date' and the rest are 'General' 

pokassov
Valued Contributor

Re: Calculated 'date' dimension exported to excel as number

that's the point. The only first column is treated by excel as a date. 

But I need to get a date format for columns with calculated dimensions 

Partner
Partner

Re: Calculated 'date' dimension exported to excel as number

I think it could be a bug because if you right click in the table and copy full table, then paste into excel, the formats are correct, so it looks like its just an export issue. 

pjtaylor
New Contributor

Re: Calculated 'date' dimension exported to excel as number

Did anyone get to the bottom of this? I have the same issue.