topic Re: Find Average hh:mm from Decimal in QlikView

Find Average hh:mm from Decimal

prees959 — Wed, 25 Nov 2020 16:16:04 GMT

I have a decimal hh:mm field and would like to calculate the actual average hours and minutes from this field.

Thank you.

Phil

jonathandienst — Tue, 19 Sep 2017 10:54:39 GMT

By decimal, I assume you mean decimal hours (ie 2.5 indicating 2h and 30 minutes).

To display as hours and minutes, divide the value by 24 and format as an interval or as a time

=Interval(myField / 24, 'hh:mm')

=Time(myField / 24, 'hh:mm')

jonathandienst — Tue, 19 Sep 2017 10:55:41 GMT

Making the same assumption, to get the hour value:

=Floor(myField)

The minute value

=Round(Frac(myField) * 60)

prees959 — Tue, 19 Sep 2017 11:02:07 GMT

great ! worked perfectly.

Additionally I need to find the average hh:mm based on this decimal field. Is this possible?

Thanks,

Phil

Or — Tue, 19 Sep 2017 11:02:20 GMT

hh:mm isn't a decimal. If that's your format, it's probably a string.

If you meant Hours.Minutes, e.g. 7.30 is 7 hours and 30 minutes - you can convert this to be measured in hours:

floor(Field) + (frac(Field)*5/3)

Which will result in a value of 7.50 (7 and a half hours).

You can then average this field, and if you want to display it in the original format, you can convert it back using

Floor(Field) + (frac(Field)*3/5)

Which in our case would convert back to 7.30.

If your value is a string, convert it to a number first and then work based on that.

prees959 — Tue, 19 Sep 2017 11:05:05 GMT

Hi,

I used your formula in this way :

Interval(avg([Updated Time] / 24), 'hh:mm')

Thanks for your help

Phil

jonathandienst — Tue, 19 Sep 2017 11:06:00 GMT

>>I need to find the average hh:mm based on this decimal field.

In the load script or front end? Average of the hours only or hours and minutes? Simplest scenario

=Interval(Avg(myField) / 24, 'hh:mm')

=Time(Avg(myField) / 24, 'hh:mm')