Formula Query for Average Duration?

Question

I am trying to use a formula query to calculate the average duration an activity is open. With a little help, I have a query to get the Count and I tried to use that query in a SumValues function to get the total duration then divide that by the count to get my average. The theory sounded good, but the results were not what I was expecting. All of the results came out in the billions. This is what I am trying, please help me find my glitch.

var text tableID = "?????????";
var number currentYear = Year(Today());
var text query = "{'140'.EX.'1'}AND{'126'.EX." & [Month] & "}AND{'138'.EX." & $currentYear & "}";
var number totalDuration = SumValues(GetRecords($query, $tableID), 117);

If([Count] > 0, $totalDuration / [Count])

------------------------------
Paul Peterson
------------------------------

MarkShnierYou · Answer

The duration is probably in some units like seconds, so there are a lot of them. I suggest that you make a numeric formula field field representing the # of hours such as ToHours([My duration field])

Change your query to query against that pure numeric field and you will know that the result is an hours. If in fact the length of your activity is really in days then you could convert the duration into days instead of hours and add up the number of days.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community