stucked with a time formate

[#1]

Hi

I find qlikview a bit tricky with time and dates but hope (as always..) to get som help from you!

I have a field wich are just 0.30 for 30 min, 0.01 for 1 min, 1.15 for 1 hour 15 min. The sum for 2 * 0.3 becomes 0.6. I need it to be 1.

Is there two diffrent ways of doing this? beacuse I´m not absolutly sure but I don´t think it need to be 1 as in 1 hour / 60 min. The user knows that 1.15 equal to 1 hour 15 min.

[Jason_Michaelides]

Hi,

In your script you need:

Time#(Floor(Time)&':'&Frac(Time)*100,'hh:mm')          AS          NewTime

See attached,

Hope this helps,

Jason

[#3]

Thanks! But it don´t seems to work over when the value goes over 24?

[Jason_Michaelides]

You didn't mention that as a requirement 😉

I guess you want it to go to days, hours and minutes then?  If so maybe something like the attached, but you are now losing the numeric nature of the field...

Jason

[#5]

😃 actually, I want it to go as hours so 28*2 = 56. I inserted 28 in the excelsheet but it shows as 08:00:00 in the application.

edit:

hmm, and now I realise that is it really timeformat I want then?

edit2:

In orginal, qv gives me 56 when I have 2*28. It´s when the 0.15 that gets wrong. So then again, it is timeformat I have to use... My head are messed up by qv 😃

[Jason_Michaelides]

Well, seeing as you're busting open Q's rules of time and space we need to go for a different approach...

See attached.  You need to convert the hh.mm number into a proper decimal of hours, then multiply it, then convert the fraction of hour back into minutes again!

The first conversion of hh.mm into decimal hours is done in the script:

Floor(Time) + Frac(Time)/0.6           AS DecimalTime

then the chart expression:

=Floor(DecimalTime*2) & ':' & Num(Frac(DecimalTime*2)*60,'00')

Obviously replace the *2 with whatever calculation you require.

Hope this helps,

Jason

[#7]

Thanks! Can I use a expression to sum all the rows with this?

[Jason_Michaelides · Accepted solution]

Floor(DecimalTime*2) & ':' & Num(Frac(DecimalTime*2)*60,'00') does not resolve to a number, so no I'm afraid.  That's what you get for messing around with the space-time continuum...

You could use a separate text box and add in some SUMs - see attached.

Jason

[#9]

well, the sum was about the orginal values so sum of the two rows 0.45 and 0.15 should be 1 and for that your solutions works well.

=Floor(SUM(DecimalTime)) & ':' & Num(Frac(SUM(DecimalTime))*60,'00')

Really nice of you to help me!

[Jason_Michaelides]

Pleasure.