Export Excel

[Guerric]

Hello,

I've encounter a problèmeusing the export to excel.

The problem concerned dates before march 1900. I've read that a date correspond to a serial number and it's the same beetween Qlik and Excel for the range between March 1, 1900 and February 28, 2100.

The problem is that all dates before march 1900, after export are one day increased in Excel (01/01/1900 become 02/01/1900).

I can fixed the problem using text formating in my visualisation but i loose the ability to ordonate the date values on Qlik.

Have you ever encountred this issue and how to solved it (if it's possbile?)

Thanks.

Guerric

[JordyWegman]

Hi Guerric,

Try this:

Date( Alt(DATEFIELD, Date#(DATEFIELD,'DD-MM-YYYY') )) as DATEFIELD

It's shown by @swuehl  in this post.

Jordy

Climber 

[Guerric]

Hi Jordy,

Thanks a lot for your quick reply. Unfortunetly, I've tried your solution and the missinterpreation by Excel still exists.

Guerric

[swuehl · Accepted solution]

I believe the issue in the other thread showing my solution is different from yours.

I think your issue may be described by

https://support.qlik.com/articles/000039011

but there is not really a solution  mentioned.

Maybe   @hic can comment on the Excel bug handling in Qlik or can show you how to export these dates correctly.

[hic]

The Gregorian calendar (the one we all use) has the following leap year rule:

1) Every fourth year is a leap year, except
2) every 100th year which is NOT a leap year, except
3) every 400th year which IS a leap year.

In other words: 1700, 1800 and 1900 were NOT leap years, but 2000 was.

The people that developed Excel did not take into account that year 1900 was not a leap year. So Excel thinks that there was a date 1900-02-29, which there wasn't. As a result, Excel can only handle dates correctly between March 1, 1900 and Feb 28, 2100.

The Qlik developers did it correctly, however. So the Qlik engine can handle dates between year 1 A.D. and far into the future.

Unfortunately I do not know of a simple way to make Excel show the correct dates. Maybe export them as text strings?

See also the white paper on https://community.qlik.com/t5/QlikView-Documents/QlikView-Date-fields/ta-p/1484786?attachment-id=120... , especially the last section called "Inter gravissimas – The Gregorian calendar".

HIC

[swuehl]

Henric and his white paper reference gave me some inspiration, as usual.

Maybe you can try with building a calendar with a separate field for a dimension used for tables that get exported to excel. Something like

LOAD 
     Recno() as DateNumQlik,
     Date(Recno()) as DateQlik,
     Dual(Date(Recno()),If(Recno()<=60,Recno()-1,Recno())) as DateQlikExcelExport,
     If(Recno()<=60,Recno()-1,Recno()) as DateExcelExportNumPart
AutoGenerate 100;

DateQlik and DateQlikExcelExport are the relevant date fields, the other two only for debugging.

Now you can use the DateQlikExcelExport date field as dimension wherever you want, it should be exported to excel correctly, though maybe as text. In Qlik, you should be able to sort the field correctly by number representation.

If you want to do any calculations on dates, though, you need to use the DateQlik field, unless you want to consider the non-existing 1900 leap year as leap year.

Something like

=Today()-DateQlik

instead of 

=Today()-DateQlikExcelExport

Hope this helps,

Stefan

[Guerric]

Hi,

Thanks for your reply and your help.

Finally the solution was to clean the original database where dates under 1901 were unrelevant!

Guerric