AGGR Syntax with IF statement and FirstSortedValue Help

[cbaqir]

I have a requirement to exclude any DEFECT_ID where:

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

but I still see many showing up with less than 1 day. What am I missing?

Select DEFECT STATUSES: New and Approved

My expression:

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(
Aggr(
  Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, 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))),MONTH_YEAR, DEFECT_ID))

TIA!

[sunny_talwar]

May be you are looking for this

If(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) > 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))

[cbaqir]

Thanks, Sunny. Let me dissect to make sure I understand...

I want to exclude those defects where the difference between the first occurrence of both selected statuses is less than 1, not where the average is less than 1, so, think I need to change the First part from this:

If(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) > 1,

to this:

If(Sum(Aggr(

Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))"}>}DISTINCT DEFECT_ID) > 1,

Does it still make sense?

If(Sum(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
> 1,
Sum(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, 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))

[sunny_talwar]

If it gives you what you want, then syntax wise it looks good to me....

[cbaqir]

I think it's closer but the calculation isn't right. I am looking for the average. If I select Oct 2013 and New and Approved, I see two DEFECT_IDs: 1111 and 1102 with Avg days 12 and 27. I would expect the average days for the month to show as 20.5 but it's showing as 2 with the new expression:

If(Sum(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
> 1,
Avg(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, 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))

[cbaqir]

I think the issue is the denominator.

Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>} DEFECT_ID)

It needs to only count those DEFECTS with >1 days between start dates.