Maximum/Minimum Number of Days a Job has been open?

[#1]

Hello!

I am trying to find out the maximum number of days a Job has been open.

I have the following info:

JobNo	CreatedDate	FinishDate	Status
1	01/01/2015	05/01/2015	Finished
2	03/01/2015	16/01/2015	Finished
3	05/01/2015	20/01/2015	Finished
4	01/01/2015		Unresolved
5	02/01/2015	17/01/2015	Unresolved.

I have some discrepancy in my data. So I would like to focus on Jobs that have the status of Finished only when finding the Min and Max.

How can I identify the shortest and longest number of days a Job has been open based on this?

[maxgro]

if you want in a text box

min or max of the difference (finish - created) by JobNo, with status finished

=min({$ <Status={Finished}>} aggr(FinishDate -CreatedDate,JobNo))

[agomes1971]

Hi,

expression for the difference days...

date#(FinishDate, 'DD/MM/YYYY') - Date#(CreatedDate, 'DD/MM/YYYY')

Min(date#(FinishDate, 'DD/MM/YYYY') - Date#(CreatedDate, 'DD/MM/YYYY'))

Max(date#(FinishDate, 'DD/MM/YYYY') - Date#(CreatedDate, 'DD/MM/YYYY'))

HTH

André Gomes

[MarcoWedel]

Hi,

one solution could be also:

=Min({$<Status={'Finished'}>} FinishDate-CreatedDate)

=Max({$<Status={'Finished'}>} FinishDate-CreatedDate)

hope this helps

regards

Marco

[jagan]

Hi,

Try like this

=Min({<Status={'Finished'}>} Floor(FinishDate-CreatedDate))

Hope this helps you.

Regards,

Jagan.