Skip to main content
Question

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

  • March 8, 2022
  • 0 replies
  • 97 views
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
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.


------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
  • JohnCrosland
  • Registered
  • March 8, 2022

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 Crosland
Inglett & Stubbs
------------------------------
Terms & ConditionsAccessibility statement