topic Calculate Duration of a Subset in App Development

Calculate Duration of a Subset

Anonymous — Sun, 01 May 2016 12:13:40 GMT

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!

Re: Calculate Duration of a Subset

Gysbert_Wassenaar — Sun, 01 May 2016 16:39:08 GMT

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

sunny_talwar — Sun, 01 May 2016 16:47:00 GMT

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

Anonymous — Mon, 02 May 2016 05:24:23 GMT

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

Anonymous — Mon, 02 May 2016 05:25:28 GMT

Hey Sunny T,

I am going to try my best!