4 Replies Latest reply: May 2, 2016 1:24 AM by Feras Alsuhaibani

# Calculate Duration of a Subset

Hello Everyone!

So I have this huge table, and in this tables there are different kind of statuses of an application. For example, App with ID: 1 has the following statuses

IDApplicationIDStatus
11New
21Under Process
31Send back to applicant
41Updated
51Finished
62New
72Under Process

So what I'd like to calculate is the average of time it takes the employees to complete assigned tasks (take the time when it is finished - the submission time), but I have to exclude any application which has not reached a specific status. For example, in the example above Application2 should be excluded.

Your help is really appreciated.

Thanks!

• ###### Re: Calculate Duration of a Subset

Something like this:

Interval(only({<ApplicationID=P({<Status={'Finished'}>}ApplicationID),Status={'Finished'}>}YourDateTimeField)-only({<ApplicationID=P({<Status={'Finished'}>}ApplicationID),Status={'New'}>}YourDateTimeField),'d hh:mm:ss')

ApplicationID=P({<Status={'Finished'}>}ApplicationID) means calculate this only for those ApplicationID values that have a status Finished. If you want to check for a different status instead then replace 'Finished' with the name of that status.

Replace YourDateTimeField with the name of the field that contains the date, time or timestamp values of the status.

Replace 'd hh:mm:ss' with the format in which you want to see the duration.

• ###### Re: Calculate Duration of a Subset

Thanks Gysbert!

I have tried what you suggested; however, I get an empty value. Gysbert, kindly, can you explain the syntax behind the suggested expression. Also can I use it with an aggregate function?

Thanks!

• ###### Re: Calculate Duration of a Subset

If what Gysbert doesn't work, would you be able to provide a sample to look at, with the expected output

• ###### Re: Calculate Duration of a Subset

Hey Sunny T,

I am going to try my best!