Date issues

[davehutchinson]

Hi all.  I've got a database that has a date field for each record, however my dashboard isn't recognising this field as a normal date, and I can't seem to rectify this in the usual way.

the field is "WeekCommencing" as is currently formatted as "DD/MM/YYYY HH:MM"

So first thing I want to do, is get rid of the time, then i want to add other fields to show week number, year etc in seperate fields:

Date([WeekCommencing],'DD/MM/YYYY') as WeekCommencing2,

WEEK([WeekCommencing]) as WeekNo,

Right([WeekCommencing2],4) as Year,
WeekName([WeekCommencing]) as YrWk,

My script is loading fine, however when I put these fields into a table they look like this:

Also similarly, if I try to put the date into a chart as a dimension, all the dates are not in order, and it doesn't seem to matter how I sort them, they never get into a numerical order.

Can anyone spot where I'm going wrong?

[lennart_mo]

Hi @davehutchinson,

could you please check for the data type of your date field 'WeekCommencing' in the data model viewer? Maybe it's formatted as text.

[davehutchinson]

Here's what it looks like, Its in the format that I was expecting.  Is there a field in the model viewer where you can change the format?

[lennart_mo]

On the left you can see the tags. If it's a date, the tags should be $num, $timestamp and possibly $date and $integer. Yours only shows $text and $ascii.

To transform it from text to dates use the following formula:

timestamp#(WeekCommencing, 'DD/MM/YYYY hh:mm') as WeekCommencing

Afterwards you should have the above mentioned tags for your field.

Using a formula date#() removes hours and minutes.

[BrunPierre]

Hi, convert to date as below and link it to a master calendar.

Date(Floor(TimeStamp#(WeekCommencing,'DD/MM/YYYY HH:MM')),'DD/MM/YYYY') as WeekCommencing2