Date format

[ToinkToinkTigger]

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

[brunobertels]

Hi 

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 

[canerkan]

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

[sidhiq91]

@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.

[ToinkToinkTigger]

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

[ToinkToinkTigger]

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?

[canerkan]

Where the columns are loaded

[ToinkToinkTigger]

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

[canerkan]

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,

[ToinkToinkTigger]

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