Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I get a time string as the following. I would like to convert it to date (mm/dd/yyyy) or time stamp.
I tried time(myTime), date(myTime), but does not work.
Could you please give any suggestion?
Thanks!
myTime
1/8/2013 0:00 |
1/8/2014 0:00 |
1/8/2015 0:00 |
1/9/2001 0:00 |
1/9/2002 0:00 |
1/9/2003 0:00 |
May be this:
Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') as Date,
Date(Frac(Date#(myTime, 'M/D/YYYY h:mm'))) as Time,
Assuming your date 1/8/2013 means January 8th 2013 (and not August 8th 2013). If it is August 8th, then use this:
Date(Floor(Date#(myTime, 'D/M/YYYY h:mm')), 'MM/DD/YYYY') as Date,
Date(Frac(Date#(myTime, 'D/M/YYYY h:mm'))) as Time,
May be this:
Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') as Date,
Date(Frac(Date#(myTime, 'M/D/YYYY h:mm'))) as Time,
Assuming your date 1/8/2013 means January 8th 2013 (and not August 8th 2013). If it is August 8th, then use this:
Date(Floor(Date#(myTime, 'D/M/YYYY h:mm')), 'MM/DD/YYYY') as Date,
Date(Frac(Date#(myTime, 'D/M/YYYY h:mm'))) as Time,
Hi Sunny , thanks so much for your help!
It works.
Hi Sunny , thanks so much for your help!
One more question about this time issue.
1/8/2013 0:00 is January 8th 2013.
I did not use: Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') in the data load editor part.
I create a dimension (dimension name is: yearDimension): Year ( Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') )
I get the following:
yearDimension:
2013
2014
2015
2001
2002
2003
And then I the above year (yearDimension) as a dimension for a time series chart.
The x-axis shows as 2013, 2014, 2015 , 2001 , 2002 , 2003. However, I would like put the year in order.
I tried the Qlik Sort (auto, numerical), but it does not work.
Could you please give any suggestion?
Is it possible to use Qlik custom Sort Expression? If so, could you please give an example?
Thanks again!
For date dimension, I suggest you to view this thread Creating A Master Calendar
It's also better if you can build and manage a date dimension in your own data warehouse and just load to Qlik, you can research calendar date dimension with Kimball technique.
Sort numerically seems to be working
Hi Sunny , thanks so much for your help!
I find it works when I change the Qlik Sorting part as:
1:Year (), check sort numerically
2: Sum(Sales)
If I changed as the following:
1: Sum(Sales)
2:Year (), check sort numerically
It will not work.
Hopefully this is helpful to other Qlik community members.
Yes, Year() needs to come first before Sum(Sales) for this to work