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

markshnier__you · 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
------------------------------

paulpeterson1 · Answer

Thanks Mark, I tried something like that, but being the geek that I am I divided the result by 86,400,000 (number of milliseconds in a day) and got results that look a little better. I'll try your approach too to see how it turns works.

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

Forum Discussion

Formula Query for Average Duration?