GetFieldSelections and Max date in Set Analysis and AGGR

[cbaqir]

How do I calculate the avg days per DEFECT_ID for the selected statuses?

SUM(
Aggr(
Max(RES_STATUS_START_DATE)- Min(RES_STATUS_START_DATE)
,DEFECT_ID)
)
  /Count({$<DEFECT_ID = {"=Count(RES_STATUS_AND_MODE) = GetSelectedCount(RES_STATUS_AND_MODE)"}>}DISTINCT DEFECT_ID)

[mdmukramali]

Hi,

Can you attach sample data?

Thanks,

Mohammed Mukram

[cbaqir]

Sample attached

If I used RES_STATUS (the one NOT in AND_MODE) I get two days.

I need the RES_STATUS_AND_MODE to show 2 days.

[Anonymous]

hi,

try this

avg(Aggr(Max(RES_STATUS_START_DATE)- Min(RES_STATUS_START_DATE),DEFECT_ID))

[cbaqir]

That gives me the same result. I think it needs to be filtered by GetFieldSelections(RES_STATUS_AND_MODE)

[Anonymous]

shouldn't need filter on RES_STATUS_AND_MODE as this selection is already considered

[cbaqir]

I need to get the Max start date from one status and the min start date from the other. In the example, it should be 2 days.