topic Re: Calculated 'date' dimension exported to excel as number in QlikView

Calculated 'date' dimension exported to excel as number

pokassov — Sat, 16 Nov 2024 05:01:51 GMT

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

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

hopkinsc — Fri, 07 Dec 2018 09:11:21 GMT

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?

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

pokassov — Fri, 07 Dec 2018 09:48:44 GMT

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!

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

hopkinsc — Fri, 07 Dec 2018 11:31:54 GMT

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..

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

hopkinsc — Fri, 07 Dec 2018 11:33:23 GMT

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

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

pokassov — Fri, 07 Dec 2018 12:18:56 GMT

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

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

hopkinsc — Fri, 07 Dec 2018 13:22:00 GMT

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.

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

pjtaylor — Thu, 18 Jul 2019 10:02:28 GMT

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