Date format in script not working in Qlik Sense Cloud?

[jonas_stratiteq]

In my load script I define the date format as:

SET DateFormat='YYYY-MM-DD';

I even specify the format during load as:

Load 
date(InvoiceDate,'YYYY-MM-DD') as InvoiceDate
FROM

Still in the data model and when using the field I get the date displayed in YYYYMMDD format. The only way I can get the format to YYYY-MM-DD is if I specifically in a visualization use date(InvoiceDate,'YYYY-MM-DD).

What can I do to not having to wrap all my date fields with date()?

[Chanty4u]

Hi 

You can convert your date format to number in script and use required date format in presentation

Load 

   Num(InvoiceDate) as InvoiceDate

FROM .

..

[jonas_stratiteq]

Thank you Chanty for your suggestion but if anything that would make it worse 🙂

The goal is to not have to add date format in presentation but get the correct date formatting done in the script.

[BrunPierre]

Hi, this perhaps?

Date(Date#(InvoiceDate, 'YYYYMMDD'), 'YYYY-MM-DD') as InvoiceDate

[jonas_stratiteq · Accepted solution]

Thank you for your suggestion but I have already tried every way I can think of to in the script make it a correct date. I have converted the date to a string with left, right, mid functions and then tried to make it a date but ended up getting the same result. I have converted it to a numeric 40k number and then turned it into a date but still the same result.

The load is a resident from two optimized QVD loads (concatenated).

I have found a workaround though.

I have to rename the fields like this to get the date format to stick, clearly a bug.. 

Load

    date(InvoiceDate) as InvoiceDate_temp

Resident

rename field InvoiceDate_temp to InvoiceDate;

[SNouguier]

Hi Jonas, 

Could it be that the source of the script you mention is a QVD (so that you have a multi layer system)?

I had the same issue, and found that if in all loading scripts from the source to final App (QVF) we set the system variable DateFormat='YYYY-MM-DD', then it behaved as expected. 

Some context: we have several layers of Qlik apps, the first reads the source and saves the result as QVD, then a second layer does the data preparation (incl. rename into business friendly names etc.) to ensure an integrated semantic layer, and the last layer is the business application. 

Hope it helps.

Kind regards,

Sébastien

[jonashertz]

Yes it is multilayered indeed.

Interesting that the date format variable could be the issue here.

Have not been able to test if it solves the issue but  I will keep it in mind if I ever encounter this again.

Thank you for the input and hopefully others might find it helpful as well.