I just received a large data set and all the dates in the Excel document are written as "MM/DD/YYYY HH:MMS". 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!
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?
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.
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.
They shouldn't. You probably missed something.
Note: presenting timestamp as a date doesn't make it a date. That is, you can present
but without actually removing the time portion (e.g. using floor), they are two different values.
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:
Load * inline
left join (Example)
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
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.