
0.59 then 1 (timeformat)
Celambarasan Adhimulam Apr 17, 2012 12:56 PM (in response to Göran Hofstedt)Hi,
Convert it as time in the load script itself like
Time(Time#(Field,'h.m'),'hh.mm')
Which keeps it in time format.
Celambarasan

0.59 then 1 (timeformat)
Göran Hofstedt Apr 17, 2012 1:28 PM (in response to Celambarasan Adhimulam )I will try first thing tomorrow when I´m back at office. So this will work even if the orginal field are 0.01 or 1024.32? The 0.01 are the lowest it can be but it can also be in for example 1024.32 as 1024 hours and 32 min.

0.59 then 1 (timeformat)
Celambarasan Adhimulam Apr 17, 2012 1:40 PM (in response to Göran Hofstedt)Hi,
In that case try with this
Interval(Interval#(Field,'h.m'),'hh.mm')
Celambarasan

Re: 0.59 then 1 (timeformat)
Marc Livingston Apr 17, 2012 2:06 PM (in response to Göran Hofstedt)Floor(Field) + ((Field  Floor(Field) )/60) *100
This will convert the minutes to 100ths allowing you to summarize.
I am assuming now after reading again that this is what you usually did, then converted back to base 60 to display it. So nevermind then

0.59 then 1 (timeformat)
Göran Hofstedt Apr 17, 2012 3:15 PM (in response to Marc Livingston)Hi marc, yes, that´s what I have tried. I think it was a bit tricky to use it in expression and sum. Maybe I´d it somehow wrong. How would you sum that field or divide it with some other field? I got it work but I think I did it more complecated then it have to be. Thanks again Celambarasan, will try it!

Re: 0.59 then 1 (timeformat)
Marc Livingston Apr 17, 2012 3:40 PM (in response to Göran Hofstedt)Since the math in qlikview is base 10 (That I am aware of) and time uses base 60 (in the decimal field) the formula I pasted above basically just converts it so instead of 60 being whole, 100 is whole. The hours section is not affected by this conversion since we are not further converting into days.
using
Floor(Field) + (((Field  Floor(Field) )/60) *100)
in any formula should calculate the correct numbers, then to display it back in the base 60 format you just reverse the signs:
Floor(Field) + (((Field  Floor(Field) )*60) /100)
Example:
time format math format 0.3 0.5 0.15 0.25 0.45 0.75 .3 + .3=1 .5 + .5= 1 5.52 + 2.36=8.28 5.87 + 5.6=8.47 I had to figure this out for our data because our data is recorded the opposite way and they wanted to see it in actual minutes

0.59 then 1 (timeformat)
Stefan Wühl Apr 17, 2012 3:51 PM (in response to Marc Livingston)I would also favor to read in the string as a QV time format (using interval#() ).
To avoid that QV reads 2.3 in as 2 hours, 3 minutes, you may need to format the minutes with two digits first (tested with QV11):
=interval(interval#(num('2.3','#.00'),'hh.mm'))
Marc, instead of using Field  floor(Field),you could also use frac(Field).
hofsted, please note that both formats (Marc's math format giving hours, and the QV interval / time format (giving days) are not compatible, so don't mix them ;)

Re: 0.59 then 1 (timeformat)
Marc Livingston Apr 17, 2012 4:01 PM (in response to Stefan Wühl )Ahh Thank you, I will need to remember that one. I had to kind of guess since for me it was a recreation of a formula I used in Crystal Reports.







0.59 then 1 (timeformat)
Göran Hofstedt Apr 18, 2012 10:22 AM (in response to Göran Hofstedt)I´m using this calculation in script.
IF(Unit='Week',Time/Period,
IF(Unit=Month,(Time/Period)/4.33 ,
(JCount*Time)/Period)) AS NewTimeField
It´s the Time that are in base 60 as orginal. What will be the best for the load? To first convert it to base 100 and then do the calculation or to do the conversion after the calculation? The calculation gives me a number with many decimal so I gues that the suggested Interval(Interval#(Field,'h.m'),'hh.mm') are not possible?
The If calculation is used to convert the time from time per day and time per month to time per week. Jcount is 17 as weekdays. Period are a number of how many times it occurs, 2 in period and week in unit are equal to every second week as are day in unit and 7 in JCount are equal to every day and so on.