Aggr syntax help

cbaqir — Tue, 25 Sep 2018 19:52:47 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 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:

Re: Aggr syntax help

cbaqir — Tue, 02 Oct 2018 22:01:11 GMT

bump

topic Re: Aggr syntax help in QlikView

Aggr syntax help

Re: Aggr syntax help