1 Reply Latest reply on Apr 6, 2011 1:02 PM by philmodjunk

how to calculate an average for time duration in todays date?

Title

how to calculate an average for time duration in todays date?

Post

have asked this questions once or twice and never got anywhere.

i have a field in a record that notes the time when the rcord is created.  i have another field where i can enter the time i stopped working on a record.  i have a calculated field that subtracts the two and successfully gives me an amount of time i was on that record.

what i want is... a field that give me an average time for all records time created in todays date. i am open to using more than one table, but i am unclear as to what to put in a second table and how to calculate that average.

i would ideally like to do this by using only a single calculation in a field, but most people think it will require a second table.  if you think this is the solution can you explain why and what fields i would need in that second table?  what the calulations might look like etc.

• 1. Re: how to calculate an average for time duration in todays date?

Method 1:

Define a summary field to compute the average of your elapsed time field.

If you perform a find for all records with today's creation date, this summary field will compute the average time spent on each record.

Method 2:

Create a self join relationship linking this table to itself by creation date. This will look like you have two tables, but you'll only have one table with two "boxes" (table occurrences) to the same table in Manage | Database | Relationships

Open Manage | Database | Relationships, select your table and click the button with two green plus signs to create a second table occurrence of your table. Link them by your creation date field. Let's name this second occurrence "SameDate".

Click the fields tab and define a calculation field with this expression to compute the average: Average ( SameDate::YourTimeIntervalField )

Now you can see the average time for a given date on any record created on that date.