Wednesday, October 5, 2011

Correct Time Arithmetic

Each time value in Qlikview has  both an external display string value and an internal numeric value. The numeric value ranges from 0 to 1 and is calculated as time/24 hours.  For example, 12 noon is represented as 0.50. 

Rounding during arithmetic can yield time values that share the same same external string but have a different underlying numeric value. The numeric value is used to group values in listboxes and chart dimensions. This is why you can sometimes see the time "01:00" display twice in a listbox or chart dimension!

Both of the time values below will display as "8:00:00 AM". But they will not group together in the same dimension bucket on a chart.

8/24 = 0.33333333333333
MakeTime(8) = 0.33333333334303


I've seen a number of methods to do time and interval calculation. Some methods will have problems with certain time values. 


Look at some examples below and how they would match up with QV times, such as those read from databases or created by QV time functions. 



MakeTime(8) =    time#('08', 'hh')     True
MakeTime(8) = 8/24 False
MakeTime(8) - MakeTime(1) = MakeTime(7) False
MakeTime(4) - MakeTime(1) = MakeTime(3) True
time#('08','hh') - 1/24 = time#('07','hh') False
time#('04','hh') - 1/24 = time#('03','hh') False
time#('08','hh') - time#('01','hh') = time#('03','hh') False
time#('12','hh') - MakeTime(3) = time#('09','hh') True
time#('08','hh') - MakeTime(1) = time#('09','hh') False
time#(time(MakeTime(8) - MakeTime(1))) = MakeTime(7) True



The only reliable and 100% consistent method is the last line. The method is: use Qlikview functions to represent times and convert the result of calculations to time strings and back to time numbers again.


Breaking down the  example: 


1. MakeTime(8) - MakeTime(1) give us a display value of  7:00:00 AM. But as you can see previously in the table, it's internal value is not exactly equal to MakeTime(7) -- the value Qlikview considers to be 7 AM.


2. Convert the value of the calculation to a string using the time() function. The result is "7:00:00 AM".


3. Convert the string into a numeric time using the time#() function. We now have the QV value for 7 AM which is equal to 7 AM read from a database or generated from any QV time function.


The same problem and solution applies to Timestamps and Intervals.


What we really need is a new "AddTime()" function, similar to the AddMonths() function. I sometimes create a variable function in my script to perform this function.


SET AddTime=time#(time($1 + $2));


Then I can use it in script or charts as:


$(AddTime(mytime,-MakeTime(1)))


-Rob