Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Format Issue

Hey,

I have date in the following format including time stamp.

29/09/2013 09:15

When I load this date field in qvw file it is represented in the following format

41487.4638888889

I want to separate time stamp and date in order to use only date as dimension.

any ideas??

17 Replies
Not applicable
Author

Tried it already but its not working.

Not applicable
Author

Its working but my number of entries have been reduced. Count of date is not the same after applying this.

juleshartley
Specialist
Specialist

What is the result?

Not applicable
Author

hi

try this

Aggr(nodistinct Date(Floor(DateField)), Date(Floor(DateField)))


*********************************************

juleshartley
Specialist
Specialist

Surely you will have fewer dates... as the granularity has been decreased to day, without time?

Sokkorn
Master
Master

What is your count expression?

Anonymous
Not applicable
Author

From the Help Manual:


DayStart(timestamp [, shift = 0 [, dayoffset = 0]])

Returns a value corresponding to a timestamp with the first millisecond of the day contained in timestamp. The default output format will be the TimestampFormat set in the script. Shift is an integer, where the value 0 indicates the day which contains date. Negative values in shift indicate preceding days and positive values indicate succeeding days. If you want to work with days not starting midnight, indicate an offset in fraction of a day in dayoffset, e.g 0.125 to denote 3am.

Examples:

daystart ( '2006-01-25 16:45' ) returns '2006-01-25 00:00:00' with an underlying numeric value corresponding to '2006-01-25 00:00:00.000'

daystart ( '2006-01-25 16:45', -1 ) returns '2006-01-24 00:00:00' with an underlying numeric value corresponding to '2006-01-24 00:00:00.000'

daystart ('2006-01-25 16:45', 0, 0.5 ) returns '2006-01-25 12:00:00' with an underlying numeric value corresponding to '2006-01-25 12:00:00.000'

Not applicable
Author

Hi,

Try using this

subfield(F1,' ',1) as date1

this is will just remove the timestamp. But your field should be date format.

DD/MM/YYYY hh:mm

And this will not reduce the count of dates.

HTH

-Shruti