0.59 then 1 (timeformat)

[#1]

Hi

I have this odd timeformat so the time 0.01 is 1 minut, 0.3 is 30 min and 1 is one hour. (With some help I got qv to convert it to decimaltime by dividing with 60 and then decimaltime to hours and min wich ended up to be pretty messy in calculations.

But I was thinking maybe some of you knows a more simply way to do this. In mather in fact the number don´t need to be converted to time because enduser already knows that 28.5 means 28 hours 50 min. So then my question seems to be can I make qlikview to count 0.059 then 0.1, 0.59 then 1 , 1.59 then 2 and so on..?  I also think that something like that could solve my problem when time goes over 23 hours it don´t continue to count right. I have understod that the asnwer to that should be Interval() but I havn´t been able to use it in same expression were I convert to hours min.

Thanks!

[CELAMBARASAN]

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

[#3]

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.

[CELAMBARASAN]

Hi,

     In that case try with this

     Interval(Interval#(Field,'h.m'),'hh.mm')

Celambarasan

[#5]

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

[#6]

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!

[#7]

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

[swuehl]

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 😉

[#9]

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.

[#10]

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 1-7 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.