Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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))
If it gives you what you want, then syntax wise it looks good to me....
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))
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.