I have a field in an Excel sheet that has date values stored in this way :
|September 20, 2016 10:31:55 AM GMT+05:30|
|October 14, 2016 8:16:52 PM GMT+05:30|
|November 21, 2016 7:05:30 PM GMT+05:30|
|November 23, 2016 6:40:42 PM GMT+05:30|
However, I need to display the date in the 'mm/dd/yy' format in my chart, without the timestamp. What should I do?
Thanks in advance,
Solved! Go to Solution.
Use Date(Your field, 'MM/DD/YY') in your load script or in front end
if you remove time stamp part from your date field completely then use
Thanks for your reply. I have tried using this expression in both the ways - in my load script or in my chart, but it does not work for some strange reason. I cannot figure why. Could it be that the field has to be formatted in a particular way in the Excel sheet itself?
If it is viable for you to remove GMT part of the field by subfield(FieldName,'GMT',1) in script, then use expression as
=date(Date#(FieldName,'MMMM DD, YYYY hh:mm:ss TT'),'DD/MM/YY')
i think this is helpful to you...
=(Date#(SubField(accepteddate,',',1) & Left(SubField(accepteddate,',',2),5),'MM-DD-YYYY'))
If it is coming as Date while loading the data in Qlik, you simply need to use below function.
Date(Floor(Fieldname),'MM/DD/YY') as NewDate
If it is coming as Text while loading the data in Qlik, then use below script.
Date(Floor(Date(Date#(Fieldname,'MMMM DD, YYYY hh:mm:ss TT'))),'MM/DD/YY') as NewDate
This works partially! There are 2 problems :
1. The formatting that you have doesn't work, this just strips off the timestamp and leaves the date behind, as-it-is.
2. The data associated with the older dates are lost.
i.e , if my table is as originally follows :
|September 20, 2016 10:31:55 AM GMT+05:30||MR00190080|
|October 14, 2016 8:16:52 PM GMT+05:30||MR00190945|
|November 21, 2016 7:05:30 PM GMT+05:30||MR00190946|
|November 23, 2016 6:40:42 PM GMT+05:30||MR00190947|
|November 23, 2016 6:40:42 PM GMT+05:30||MR00190948|
|October 14, 2016 8:16:52 PM GMT+05:30||MR00190949|
There are 2 defects logged on October 14. But now, with the timestamp being stripped off, when I plot, for example, Count([Defect ID]) against this new date as dimension, the values are all wrong.
I have attached the sample Excel for you to look at. What I want is to strip off the time stamp from the date field and plot the number of Defect IDs on a particular date as a bar graph.