Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prees959
Creator II
Creator II

Find Average hh:mm from Decimal

HI

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

Thank you.

Phil

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Making the same assumption, to get the hour value:

=Floor(myField)

The minute value

=Round(Frac(myField) * 60)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
prees959
Creator II
Creator II
Author

great ! worked perfectly.

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

Thanks,

Phil

Or
MVP
MVP

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
Creator II
Creator II
Author

Hi,

I used your formula in this way  :

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

Thanks for your help

Phil

jonathandienst
Partner - Champion III
Partner - Champion III

>>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')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein