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)))
