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#(’08’,’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
nice , thanx for sharing