topic Re: Date format in script not working in Qlik Sense Cloud? in App Development

Date format in script not working in Qlik Sense Cloud?

jonas_stratiteq — Wed, 24 May 2023 13:26:52 GMT

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()?

Re: Date format in script not working in Qlik Sense Cloud?

Chanty4u — Thu, 25 May 2023 06:43:44 GMT

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

Load

Num(InvoiceDate) as InvoiceDate

FROM .

Re: Date format in script not working in Qlik Sense Cloud?

jonas_stratiteq — Fri, 26 May 2023 09:05:07 GMT

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.

Re: Date format in script not working in Qlik Sense Cloud?

BrunPierre — Fri, 26 May 2023 09:23:51 GMT

Hi, this perhaps?

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

Re: Date format in script not working in Qlik Sense Cloud?

jonas_stratiteq — Wed, 14 Jun 2023 12:08:41 GMT

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;

Re: Date format in script not working in Qlik Sense Cloud?

SNouguier — Mon, 11 Sep 2023 13:15:40 GMT

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

Re: Date format in script not working in Qlik Sense Cloud?

jonashertz — Tue, 12 Sep 2023 12:54:28 GMT

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.