Requirement: Combo Chart Avg number of DEFECT_ID per month per GOV_DECISION_BODY_SUB based on associated Agenda Date.
Grouped by Calendar year
y-axis - avg number of tickets per month
x-axis GOV_DECISION_BODY_SUB
legend Year
Combo chart
vert bars - each year
line for how many DEFECT_ID each year only saw 1 group (only 1 GOV_DECISION_BODY_SUB per DEFECT_ID)
Any DEFECT_ID can have up to 6 GOV_DECISION_BODY_SUB values. For each GOV_DECISION_BODY_SUB, I need to pull the associated Agenda Date.
In some cases, the GOV_DECISION_BODY_SUB will match X_DECISION_BODY (Primary, Secondary, Third etc) and in other cases GOV_DECISION_BODY_SUB will match X_DECISION_COMMITTEE_CAT (Primary, Secondary, Third etc).
For example, DEFECT_ID = 12407 has two GOV_DECISION_BODY_SUB values: ECG and ACG - Case Management.
ECG is the PRIMARY_DECISION_BODY and the PRIMARY_AGENDA_DATE is 4/22/2019.
ACG - Case Management is the SECONDARY_DECISION_COMMITTEE_CAT and the SECONDARY_AGENDA_DATE is 5/18/19.
If this were the only DEFECT_ID, I would need to show an average of 1 ticket for ECG for April 2019 and 1 ticket for ACG - Case Management for May 2019.
Obviously, there will be many DEFECT_IDs for each Agenda Date Month and each GO.V_DECISION_BODY_SUB. How do I accomplish this?
