Calculate Duration of a Subset

[Anonymous]

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

ID	ApplicationID	Status
1	1	New
2	1	Under Process
3	1	Send back to applicant
4	1	Updated
5	1	Finished
6	2	New
7	2	Under 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!

[Gysbert_Wassenaar]

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.

[sunny_talwar]

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

[Anonymous]

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!

[Anonymous]

Hey Sunny T,

I am going to try my best!