Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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))
I can't hardcode the status because it should apply to both selected statuses and the user can pick any 2.
I am not sure I am completely understanding what you need here?
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))
Thanks!