Complicated AGGR

cbaqir — Tue, 04 Sep 2018 20:55:52 GMT

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.

Generic resources = RESOURCE with a "_" in the name like charges_builder.

Requirement: avg number of days from when a generic build resource is populated to the FIRST time a named resource is populated
Requirement: IGNORE generic name of ccl_consult

Requirement: if no "_" (generic) resource, use start date of COMMITTEE_CAT = "ERC - Assigned" to first date name is populated

One DEFECT_ID could have multiple generics or multiple names for each RESOURCE_TYPE

Example: DEFECT_ID 9787 should be 102 days - 3/29/18 through 7/9/18 for the Resource Type of CCL Resource.

It should also show a calculation for the Resource Type of Primary Assignee but in this case it's also from 3/29/18-7/9/18

How do I write the expression to give me the time that a specific RESOURCE_TYPE went from generic to named?

This did not return anything:

SUM( {<[ccl_consult]=>}
Aggr(Only({<[ccl_consult]=>}
( Max(FACT_ALM_DEFECT_RESOURCE.START_DATE)- Min({<FACT_ALM_DEFECT_RESOURCE.RESOURCE = {'*_*'}>}FACT_ALM_DEFECT_RESOURCE.START_DATE)))
,DEFECT_ID,DM_STAT_ALM_DEFECT_BUILD_RESOURCE.RESOURCE)
)

topic Complicated AGGR in QlikView

Complicated AGGR