AGGR with Variable Dimension

cbaqir — Sat, 16 Nov 2024 04:12:55 GMT

I am trying to show Average days turnaround aggregated by month and I am having difficulty with the syntax, especially when trying to use a variable.

It works at the transaction level (per DEFECT_ID) but not when aggregated.

Please see attached.

Expression: =aggr(avg(if(PRIMARY_DECISION_BODY = 'PDG', if(isnull(PRIMARY_DECISION_APPROVED_DATE),if(today()>= date(PRIMARY_DECISION_AGENDA_DATE),today()- date(PRIMARY_DECISION_AGENDA_DATE)),
date(PRIMARY_DECISION_APPROVED_DATE) - date(PRIMARY_DECISION_AGENDA_DATE)),
if(SECONDARY_DECISION_BODY = 'PDG', if(isnull(SECONDARY_DECISION_APPROVED_DATE),if(today()>= date(PRIMARY_DECISION_AGENDA_DATE),today()- date(SECONDARY_DECISION_AGENDA_DATE)),
date(SECONDARY_DECISION_APPROVED_DATE) - date(SECONDARY_DECISION_AGENDA_DATE)),
if(THIRD_DECISION_BODY = 'PDG', if(isnull(THIRD_DECISION_APPROVED_DATE),if(today()>= date(PRIMARY_DECISION_AGENDA_DATE),today()- date(THIRD_DECISION_AGENDA_DATE)),
date(THIRD_DECISION_APPROVED_DATE) - date(THIRD_DECISION_AGENDA_DATE)),
if(FOURTH_DECISION_BODY = 'PDG', if(isnull(FOURTH_DECISION_APPROVED_DATE),if(today()>= date(PRIMARY_DECISION_AGENDA_DATE),today()- date(FOURTH_DECISION_AGENDA_DATE)),
date(FOURTH_DECISION_APPROVED_DATE) - date(FOURTH_DECISION_AGENDA_DATE)),
if(FIFTH_DECISION_BODY = 'PDG', if(isnull(FIFTH_DECISION_APPROVED_DATE),if(today()>= date(PRIMARY_DECISION_AGENDA_DATE),today()- date(FIFTH_DECISION_AGENDA_DATE)),
date(FIFTH_DECISION_APPROVED_DATE) - date(FIFTH_DECISION_AGENDA_DATE))
))))),$(vPDGMonthStart)))

topic AGGR with Variable Dimension in QlikView

AGGR with Variable Dimension

Re: AGGR with Variable Dimension