Feature Request : AverageValues Formula Query function similar to SumValues for use in Formula Queries

Question

I'm interested in an AverageValues() function similar to SumValues() for use with GetRecords() where the value type of the field is 'Time of Day'. Since there is an Average function that accepts Time of Day values, I'd like to be able to feed it the list of values returned from GetRecords() to calculate the average.

If there is another way to perform this, please let me know.

Thanks,

------------------------------
Joseph Gomez
------------------------------

markshnier__you · Answer

Well, under the motto of don't let the things that you cannot do stop you from doing the things that you can, I offer the suggestion.

You can use a formula to convert the time of day field to the number of hours past midnight, using a formula numeric field.

Then you can use a formula query to get a total of those values, and also use a formula query to get a count of those values, and then divide one by the other to get your average.

Once you have that you can turn it back to a time of day using a formula.

johncrosland · Answer

Good morning,If you want to do this via Formula Queries, then you can convert the time of day into decimal hours. You can then use a Sum Values on that field and then divide by the count to get the average. If you need it to be in a TimeOfDay format, then you can convert it back into TimeOfDay. If you need to use a Formula Query because you need for it to be valid for many records in a different table, then I would suggest creating a 1 record table and connect it to the records in the Table you are trying to Average as a Parent, and then you can use the Average Summary Field Function, and then you can use a Formula Query to retrieve that single value. If you need it in the Child Table of that then you can just use a Lookup field.Good Luck!
John------------------------------John CroslandInglett &amp; Stubbs------------------------------

josephgomez · Answer

Thanks, Mark. I managed to implement this using a formula field, then in summary reports via summary formula for avg, then converted back to ToD value. Appreciate it.

------------------------------
Joseph Gomez
------------------------------

josephgomez · Answer

Thanks, John. I knew I just need a bit of guidance to get it to work. Appreciate it.------------------------------Joseph Gomez------------------------------

Forum Discussion

Feature Request : AverageValues Formula Query function similar to SumValues for use in Formula Queries

4 Replies