Duration from First Occurence

[cbaqir]

Select DEFECT_ID = 1073 and Statuses of New and Approved.

For this defect, the start date for new is 9/23/13 and there are two instances of Approved... one with a start date of 11/11/2013 and one with 11/25/13.

Right now the expression is showing me 63 days which is 11/25/13-9/23/13.

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(
Aggr(
Max(DEFECT_STATUS_START_DATE)- Min(DEFECT_STATUS_START_DATE)
,DEFECT_ID)
)

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))),FISCAL_YEAR, DEFECT_ID))

I only want the first occurrence of whichever Statuses (DEFECT_STATUS_MAP) are selected. In this example, I would want to see 49 days which is 11/11/2013-9/23/13.

TIA!

stalwar1

[sunny_talwar]

May be this

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(

  Aggr(

  Min({<DEFECT_STATUS = {'Approved'}>} DEFECT_STATUS_START_DATE)- Min(DEFECT_STATUS_START_DATE)

  ,DEFECT_ID)

)

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))),FISCAL_YEAR, DEFECT_ID))

[cbaqir]

I can't hardcode the status because it should apply to both selected statuses and the user can pick any 2.

[sunny_talwar]

I am not sure I am completely understanding what you need here?

[Nicole-Smith · Accepted solution]

I believe this works how you want it to (it returns 49 for me, and the FirstSortedValue will take into consideration any status that is selected):

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(

  Aggr(

  Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE)

  ,DEFECT_ID)

)

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))),FISCAL_YEAR, DEFECT_ID))

[cbaqir]

Thanks!