I have a requirement to show avg number of days from when a generic build resource is populated to when a named resource is populated PER DEFECT_ID.
Generic resources = RESOURCE with a "_" in the name like clindoc_builder.
Requirement: if no "_" (generic) resource, use start date of RES_STATUS = "ERC - Resourcing" to first date name is populated
=if (count({<FACT_ALM_DEFECT_RESOURCE.RESOURCE = {'*_*'}>}FACT_ALM_DEFECT_RESOURCE.START_DATE)=0,
avg(aggr(
Min(date(FACT_ALM_DEFECT_RESOURCE.START_DATE),'MM/DD/YY')-min({<RES_STATUS = {'ERC - Resourcing'}>}RES_STATUS_START_DATE)
,DEFECT_ID,[FACT_ALM_DEFECT_RESOURCE.RESOURCE TYPE],FACT_ALM_DEFECT_RESOURCE.RESOURCE)
),
(SUM(
Aggr(
Min({<FACT_ALM_DEFECT_RESOURCE.RESOURCE -= {'*_*'}>}date(FACT_ALM_DEFECT_RESOURCE.START_DATE),'MM/DD/YY')-
Min(date(FACT_ALM_DEFECT_RESOURCE.START_DATE),'MM/DD/YY'),DEFECT_ID)
))
/count(DISTINCT DEFECT_ID))
The aggr looks right but the calculation for DEFECT_ID 9986 should be 16 days - 4/25/18-5/11/18 - NOT 8 or 24.
This should only show 16: