Need help fixing if statement for bar graph

[ckiesling]

the idea is that different things need to happen based on if there is a discharge date present vs. when there isn't a discharge date present. AND different things need to happen based on the contact type-- if the contact type is phone/face to face there will be a bar in the bar graph that says complete and if the contact type is attempted/no contact there will be another bar in the bar graph that says incomplete.

This formula says OK, but it is not showing complete and incomplete bars like I want it to.

=if(
(
not isnull([Client Hospitalizations.Discharge Date]) AND
(
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'Face-To-Face' OR
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'Phone'
) AND
[Client Hallmark Events.Type Code Display Name] = 'Hospitalization - General' AND
[Client Hospitalizations.Followup Contact Type Code Display Name] <> 'Deceased' AND
(
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Transferred to another hospital' AND
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Not yet discharged'
) AND
not isnull([Client Transition.Transmission Date]) AND
[Client Transition.Transmission Date] - [Client Hospitalizations.Discharge Date] <= 3
)
OR
(
isnull([Client Hospitalizations.Discharge Date]) AND
not isnull([Episode.End Date]) AND
(
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'Face-To-Face' OR
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'Phone'
) AND
[Client Hallmark Events.Type Code Display Name] = 'Hospitalization - General' AND
[Client Hospitalizations.Followup Contact Type Code Display Name] <> 'Deceased' AND
(
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Transferred to another hospital' AND
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Not yet discharged'
) AND
not isnull([Client Transition.Transmission Date]) AND
[Client Transition.Transmission Date] - [Episode.End Date] <= 3
),
'Complete',
if(
(
not isnull([Client Hospitalizations.Discharge Date]) AND
(
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'Attempted' OR
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'No Contact'
) AND
[Client Hallmark Events.Type Code Display Name] = 'Hospitalization - General' AND
[Client Hospitalizations.Followup Contact Type Code Display Name] <> 'Deceased' AND
(
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Transferred to another hospital' AND
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Not yet discharged'
) AND
not isnull([Client Transition.Transmission Date]) AND
[Client Transition.Transmission Date] - [Client Hospitalizations.Discharge Date] <= 3
)
OR
(
isnull([Client Hospitalizations.Discharge Date]) AND
not isnull([Episode.End Date]) AND
(
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'Attempted' OR
[Client Hospitalizations.Followup Contact Type Code Display Name] = 'No Contact'
) AND
[Client Hallmark Events.Type Code Display Name] = 'Hospitalization - General' AND
[Client Hospitalizations.Followup Contact Type Code Display Name] <> 'Deceased' AND
(
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Transferred to another hospital' AND
[Client Hospitalizations.Followup Medication Reconciliation Code Display Name] <> 'Not yet discharged'
) AND
not isnull([Client Transition.Transmission Date]) AND
[Client Transition.Transmission Date] - [Episode.End Date] <= 3
),
'Incomplete'
)
)

[G3S]

please provide some sample data and a mock up of the expected chart

[marcus_sommer]

I assume this expression is not a singular query-approach else repeated in various versions within all kind of visualizations. Even if not all have so many loops it would be sensible to flag and to group/categorize all essential associations within the data-model. It might there cause some efforts but doing it within the UI will result in much more painful work.