Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a field in an Excel sheet that has date values stored in this way :
Accepted Date |
---|
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,
Kanchana
Try this
Date(Floor(Date(Date#(Left(AcceptedOn,Len(AcceptedOn)-10),'MMMM DD, YYYY hh:mm:ss TT'))),'DD/MM/YYYY')
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
Date(Floor(your field),'MM/DD/YY')
Hi Kiran,
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?
Thanks,
Kanchana
Can you post a sample excel with the date fields or your sample qvw
Hi
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')
Hi,
i think this is helpful to you...
try this...
=(Date#(SubField(accepteddate,',',1) & Left(SubField(accepteddate,',',2),5),'MM-DD-YYYY'))
Hi,
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
Regards,
Kaushik Solanki
Can you send me the sample data, so that it would help to solve
Hi Kalyani,
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 :
Date | Defect ID |
---|---|
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.
Regards,
Kanchana
Hi,
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.
Regards,
Kanchana