Simplifying date and timestamp

[#1]

I just received a large data set and all the dates in the Excel document are written as "MM/DD/YYYY HH:MM:SS". This is problematic because when I load the data and create a line chart, for example, each entry is listed as a separate date if they were created on the same day but the time they were created was different. Do I need to address this in Excel or is there a way for me to use QlikSense to only look at the MM/DD/YYYY part?

Thanks for your help in advance!

[sunny_talwar]

Try this:

Date(Floor(Date#(DateField, 'MM/DD/YYYY hh:mm:ss')), 'MM/DD/YYYY') as DateField

[#3]

This worked to eliminate the 'hh:mm:ss' from the dates - so a big thank you - but when I put it into a line chart each of the different entries on 8/18/2014, for example, still show up as unique dates because of the different times they were entered. Do you know how to address that issue?

[maxgro]

floor as sunindia suggested, should remove the time part

see the difference between the 3rd (num, no floor, with time) and 4th (floor, without time) row

[#5]

That makes sense. So am I entering this formula into the formula bar for the dimension of my line chart or am I entering this into the data load editor?

[Anonymous]

If you never need the "time" portion, it is certainly better to do it on the data load.  Not only it simplifies the field - it reduces application size, sometimes significantly, if you have a large amount of the records containing timestamps.

[#7]

That makes sense. Do you know why all the entries with identical dates but different time portions are remaining unique, even after I do this?

[sunny_talwar]

‌So are you saying that when you create a list box for date, you see multiple entries for the same date? If that's true can you post your script to look at? It is hard to know without seeing it.

[Anonymous]

They shouldn't.  You probably missed something.

Note: presenting timestamp as a date doesn't make it a date.  That is, you can present

06/26/2015 07:25:00

and

06/26/2015 07:25:01

as 06/26/2015

but without actually removing the time portion (e.g. using floor), they are two different values.

[#10]

Hi Daniel

If you are still seeing multiple values it is possible you have another field that is causing each value to display rather than just displaying one entry for each unique date.

Everyone above is quite right that using the floor function to remove the time portion will truncate the timestamp leaving just the date element.  In fact if you format this as a timestamp you would get e.g. 23/10/2014 00:00:00 as there is no time element left after the floor function has been used.

If there is another field in your table that has a different value against each of the identical truncated dates then this will cause more than one of the date values to show in your table.

By example, a simple script of:

Example:
Load * inline
[
ID,Date
1,23/09/1998 06:03:31
2,09/05/2013 10:50:02
3,13/06/2003 15:30:53
4,27/01/2006 00:42:43
5,03/01/2007 06:26:44
6,18/02/2011 07:56:22
7,02/11/2000 03:18:09
8,26/01/2002 08:32:15
9,27/01/2006 00:42:43
10,03/01/2007 06:26:44
11,18/02/2011 09:42:20
12,02/11/2000 03:18:09
13,26/01/2002 09:45:17
14,27/01/2006 23:53:10
15,03/01/2007 06:26:44
16,18/02/2011 07:22:54
17,02/11/2000 03:18:09
18,26/01/2002 08:29:42
19,27/01/2006 00:42:43
21,03/01/2007 06:26:44
];

left join (Example)
Load
ID,
Timestamp(Date,'DD/MM/YYYY hh:mm:ss') as FormattedTimestamp,
Timestamp(Floor(Date),'DD/MM/YYYY hh:mm:ss') as FlooredTimestamp,
Date(Floor(Date),'DD/MM/YYYY') as TruncatedDate
Resident Example;

will generate a few dates to play with.

You will see that the ID field is unique for each date but there are some dates where the date is the same but the time is different.

Dropping these fields into a table will result in:

You can see here how the floor function has removed the time element and then by formatting the floored result as a date in DD/MM/YYYY format you get the date result you want.  However, you will also see that some of the dates are present more than once in the right most column.  This is because the ID field is unique to the dates and as the ID field is included in the table then each related occurrence of the date will be displayed.  To prevent the multiple dates from showing up you would need to remove the unique fields.  So by removing the ID and formatted timestamp fields the result is:

and the multiple values have gone.

Not sure if this helps but hopefully it will.

Kind regards

Steve