topic Re: Time rounding problem in QlikView

Time rounding problem

Mon, 30 Jun 2014 07:16:57 GMT

Hi,

I'm trying to convert an input time that is provided as a decial number (e.g. 2.28, format is hours.minutes).

This shall be converted to 2:16, for 2 hours and 16 minutes This is my formula:

=interval($(v_volume_fn)/24, 'hh:mm')&' Std.'

My problem is, that the calculated minute is not commercially rounded, but instead always rounded off (i.e. decimal values are cut off)

e.g. 2.28 is calculated to 2:16 (h:mm) (0,28*60 = 16.8 => 16)

but it should be

2.28 is calculated to 2:17 (h:mm) (0,28*60 = 16.8 = 17)

So the calculated minute is commercially rounded (i.e. [<0.5: round off]; [>=0.5: round up])

How can I achieve this?

Thanks

Re: Time rounding problem

nirav_bhimani — Mon, 30 Jun 2014 07:41:26 GMT

Hi,

You can try this

=IF (Right (Num#(CalculatedValue,'# ##0.0'),1) <=5, Floor(CalculatedValue), Ceil(CalculatedValue))

For e.g.

=IF (Right (Num#(16.5,'# ##0.0'),1) <=5, Floor(16.5), Ceil(16.5))

Returns 16.

Is that you want.

Regards,

Nirav Bhimani

Re: Time rounding problem

jonathandienst — Mon, 30 Jun 2014 08:06:15 GMT

This will does it:

=Interval(MakeTime(Floor(2.28), Round(Frac(2.28)*60)))

Gives 2:17. Just insert your field in place of 2.28

HTH

Re: Time rounding problem

Mon, 30 Jun 2014 08:19:20 GMT

Hi Jonathan,

this is exactly what I need. Many thanks!

Re: Time rounding problem

Mon, 30 Jun 2014 09:04:52 GMT

I found another issue.

The variable is used to record an amount of time, not limited by one day. With the maketime function, an amount of let's say 25 hours and 34 mins is set to NULL.

How can I handle this?

Re: Time rounding problem

Mon, 30 Jun 2014 09:07:49 GMT

Perhaps like this?

=interval(Floor($(v_volume_fn)) & ':' & Round(Frac($(v_volume_fn))*60), 'hh:mm')&' Std.'