Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

stucked with a time formate

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.

1 Solution

Accepted Solutions
jason_michaelid
Not applicable

Re: stucked with a time formate

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 Replies
jason_michaelid
Not applicable

Re: stucked with a time formate

Hi,

In your script you need:

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

See attached,

Hope this helps,

Jason

Not applicable

stucked with a time formate

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

jason_michaelid
Not applicable

Re: stucked with a time formate

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

Not applicable

Re: stucked with a time formate

=) 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_michaelid
Not applicable

Re: stucked with a time formate

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

Not applicable

stucked with a time formate

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

jason_michaelid
Not applicable

Re: stucked with a time formate

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

Not applicable

stucked with a time formate

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_michaelid
Not applicable

stucked with a time formate

Pleasure.