Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

Export to EXCEL a Dimension DATE column like DATE format and not TEXT

Hi

I migrated to version 11.00 SR1 and seems that now, when we exported to Excel a Table with DATE columns , they are in text format(right click on cell->format cell-> is Text instead Date or Custom).

it's strange because Expression DATE Columns in Excel are formatted as Date but Dimension Date Column are formatted as Text.

in Dimension I tied to use functon DATE() but nothing changed.

there is a solution?

many thanks and best regards,

Kinney

3 Replies
er_mohit
Honored Contributor II

Re: Export to EXCEL a Dimension DATE column like DATE format and not TEXT

Try to use Date(Date#(datefield,'DD-MM-YYYY'),'DD/MM/YYYY')

here in this Datefield is my fieldname first format is that which comes in field then the 2nd format is that which i want now

hope it helps you try to use this in dimension

if your problem doesn't resolve then try it in script

hope it helps...

Not applicable

Re: Export to EXCEL a Dimension DATE column like DATE format and not TEXT

many thanks for your answer.

unfortunatly this doesn't works.

in the script i've already formast filed as Date.

thanks again,

kinney

mellerbeck
Contributor II

Re: Export to EXCEL a Dimension DATE column like DATE format and not TEXT

Hi Brian, I just fought with this for a bit. What worked for me was using a date() during the load script, but do not use a date() around the dimension. Once I removed the date() wrapped around the dimension it started exporting correctly. That being said I did contact support and they have a case titled "Chart is exported as text instead of numbers to Excel"

“The expression used in the chart generates a column with mixed formats, by applying individual formats from the indata Format field. Some values have decimals and other do not. This leads to the column not being defined as decimal numbers, but instead as a mixed column.

  Mixed values are exported as Text, which happens in this case. This can also been seen when checking the Excel cells format value. This design decision was taken long ago and specifically to simply the export process and speed it up, the result of many customer complaints regarding the export to excel being extremely slow. So reverting to the old, abandoned method of exporting and that will not happen.

The solution is to have the same format for the entire column or manually reformat the fields in Excel.”

So, if you have mixed values that would also throw it off I would suppose!