topic Re: Date format in App Development

Date format

ToinkToinkTigger — Fri, 23 Sep 2022 09:16:16 GMT

Hi there,

I have a column with date values. The format from the source is:

6/7/2022 08:49 PM Europe/Berlin

I would like to use the built-in calendar measurement in QS, however it does not regognize values as a date value.

Tried to split the field into pieces to extract the ''D/M/YYYY" however, did not get this to work.

Perhaps I can change the date format in the script loader? Would appreciate some help 🙂 Thanks

Re: Date format

brunobertels — Fri, 23 Sep 2022 09:33:41 GMT

try this

subfield('6/7/2022 08:49 PM Europe/Berlin',' ',1)

Extract the part before the first blank space so here 6/7/2022

then to insure the date format of the expression :

date(date#(subfield('6/7/2022 08:49 PM Europe/Berlin',' ',1),'DD/MM/YYYY'),'DD/MM/YYYY')

hope it helps

Re: Date format

canerkan — Fri, 23 Sep 2022 09:40:07 GMT

Hi ToinkToinkTigger,

First you have to find out what data type your field has. Is it a string or date or Timestamp?

Timestamps are decimal numbers and date values are integers. Therfore changing the format might change it's appearance but not the value itself and that could be the reason your Field is not recognised as a date value. You have to floor your timestamp value to get an integer value of the date. Try adding this line to your load statement in your load script:

Date(Floor(YourDateField)) as Date

If it's not working your field might be a string value and you might need to extract the date part from your field beforehand and then use this instead:

Date(Floor(Date#(YourDateStringField))) as Date

Let me know if it helped.

Regards

Can

Re: Date format

sidhiq91 — Fri, 23 Sep 2022 11:12:40 GMT

@ToinkToinkTigger Please use the below code in the script load editor:

NoConcatenate
Temp:
Load Date(Date#(SubField(Date,' ',1),'D/M/YYYY'),'DD/MM/YYYY') as Date

inline [
Date
6/7/2022 08:49 PM Europe/Berlin
];

Exit Script;

If this resolves the issue, please like and accept it as a solution.

Re: Date format

ToinkToinkTigger — Fri, 23 Sep 2022 11:23:07 GMT

I tried this line of code, but it results in a corrupted dashboard (no data visible)

Re: Date format

ToinkToinkTigger — Fri, 23 Sep 2022 11:26:02 GMT

Thanks, where exactely do I put this code? In the main of the editer loader where dateformat is set or perhaps the editor where the column is being loaded?

Re: Date format

canerkan — Fri, 23 Sep 2022 11:27:17 GMT

Where the columns are loaded

Re: Date format

ToinkToinkTigger — Fri, 23 Sep 2022 11:30:39 GMT

So how would that look like?, the column with the date fields is ''completion date''

Re: Date format

canerkan — Fri, 23 Sep 2022 11:45:46 GMT

like this in the first solution:

Date(Floor("Completion Date")) as CompletionDate,

But for the second solution you would need to extract the string date beforehand, like @sidhiq91 suggested. Maybe like this:

Date(Floor(Date#(SubField("Completion Date",' ',1),'D/M/YYYY'))) as CompletionDate,

Re: Date format

ToinkToinkTigger — Fri, 23 Sep 2022 11:58:45 GMT

Unfortunately both solutions dont work... Still not able to make a calendar measurement.

Re: Date format

canerkan — Fri, 23 Sep 2022 12:05:11 GMT

But are Date values in your column? If your create a straight table with this column, what values are displayed?

Re: Date format

ToinkToinkTigger — Fri, 23 Sep 2022 13:57:42 GMT

This is how the values are displayed now after putting in the line of code. Looks like a date format, but still not detected by QS.

Re: Date format

canerkan — Mon, 26 Sep 2022 06:09:24 GMT

Maybe there could be a problem if your standard date format in your main script part does not match the format. Have you checked that it's the same format?

Re: Date format

ToinkToinkTigger — Mon, 26 Sep 2022 07:57:36 GMT

Hi Good morning. This is how it looks right now.

Good news is that I have a column with a date now in MM/DD/YYYY format.

Still cannot use the calendar measurement function in Qlik as it keeps saying the field is not a data field.

Re: Date format

brunobertels — Mon, 26 Sep 2022 08:01:21 GMT

you need to Name your dimension :

Date(......subfield(....))) AS "Completion date" ,

Re: Date format

ToinkToinkTigger — Mon, 26 Sep 2022 08:07:32 GMT

Hi, sorry for being a noob... but what is the full line of code? tried several things but just getting an error after loading.

Re: Date format

canerkan — Mon, 26 Sep 2022 08:17:15 GMT

This is the line you need to user in your load script:

Date(Date#(SubField("Completion Date",' ',1),'D/M/YYYY')) as "Completion Date",

Then use Dimension "Completion Date" in your Visualization.

If it's still not working, can you provide some sample data?

Re: Date format

ToinkToinkTigger — Mon, 26 Sep 2022 08:24:52 GMT

So the date fields are now being displayed properly. However, still no options to categorize by year or month etc. How do we achieve this? Normally the calendar function does this. Which is not working.

Re: Date format

canerkan — Mon, 26 Sep 2022 08:39:50 GMT

You can do that yourself by using Date Functions like Month etc. in your load script. Or create a Mastercalendar.

Check the link below for a pretty good collection of information to that topic by @marcus_sommer :

https://community.qlik.com/t5/QlikView-Documents/How-to-use-Master-Calendar-and-Date-Values/ta-p/1495741

Re: Date format

ToinkToinkTigger — Mon, 26 Sep 2022 11:45:27 GMT

Okay Thanks everyone. So in Summary the lines of codes suggested fixed my issue. The auto calendar feature is now working after reloading the script and data source again.

Thanks everyone for helping out. Until the next one 🙂