Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
someone asked me to display data of the last 24 hours. not just the last day, but 24 hours from 9am on to 9am.
is it possible, to change the time which qlikview defines as "day" (1.1. 0:00-23:59) to (1.1. 9:00-2.1. 9:00)
so the "day" is the time between 9:00am yesterday and 9:00am today ?
or is there an expression to limit the "window of time" which is used in calculations ?
happy new year,
alex
Yes, use daystart with appropriate second and third parameter (best to create a new field in your data model for your CustomDayDate )
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'
wow, that was incredibly fast.
this could be the solution, but its not working as described in the reference guide
=DayStart('05.01.2012', 0, 0.375) works fine, returns '05.01.2012 9:00'
BUT
=DayStart('05.01.2012 05:00', 0, 0.375) returns '-' , so its not working
QV 9 (x86)
I haven't tried it on QV9, but I assume you need to state
=DayStart('05.01.2012 05:00:00', 0, 0.375)
(i.e. use seconds to get your timestamp correctly parsed in). This will return:
04.01.2012 09:00:00