I have data showing client alcohol use over several periods: Baseline, 3 Month, and 6 Months. My goal is to create a chart showing the count and percentage of clients who used alcohol at each time period. Chart users should be able to select different time periods to compare alcohol use.
There are two requirements:
- If the chart user wants to compare specific periods (ex. Baseline vs. 3 Month), only clients having records for both time periods (Baseline and 3 Month) in the table should be used. Clients with only Baseline records should be excluded in this example. (Have a workable solution for this requirement)
- Only clients who responded ‘Yes’ or ‘No’ to the alcohol use question at all selected periods should be used. Clients with ‘No Response’ to the alcohol use question should be excluded. (Having trouble figuring out how to meet this requirement)
A sample document with chart is attached. The data looks like this:
LOAD * INLINE [
Client, Period, UsedAlcohol, ClientHasBaseline, ClientHas3Month, ClientHas6Month
1, Baseline, Yes, Yes, Yes, Yes
1, 3 Month, Yes, Yes, Yes, Yes
1, 6 Month, No, Yes, Yes, Yes
2, Baseline, Yes, Yes, Yes, No
2, 3 Month, No, Yes, Yes, No
3, Baseline, Yes, Yes, Yes, No
3, 3 Month, No Response, Yes, Yes, No
4, Baseline, Yes, Yes, No, No
];
I created the ClientHasBaseline, ClientHas3Month, and ClientHas6Month fields to be used to restrict the data to only those clients who have records for the periods to be compared. This is not the best solution because the chart user has to select the periods of interest and then additionally make selections on the ClientHasBaseline, ClientHas3Month, ClientHas6Month fields to restrict to those clients who have records for all desired periods. This solution works, however, and I can probably clean this up later using triggers to automatically make selections on the ClientHasBaseline, ClientHas3Month, ClientHas6Month fields.
I haven’t been able to figure out how to restrict the clients to only those who responded ‘Yes’ or ‘No’ to the alcohol use question at all selected periods.
Any ideas on how to make this work? I would like to avoid using another flag field because the actual dataset has a very large number of questions, and I do not want to create something like a ‘ClientRespondedAtAllPeriods’ flag for each question.
In my sample document, if the chart user wants to compare alcohol use at Baseline and 3 Months, then only Clients 1 and 2 should be used because both clients have Baseline and 3 Month records, and both clients responded ‘Yes’ or ‘No’ to the alcohol use question at both periods. In this case alcohol use at Baseline should be 100% (2 out of 2 clients), and at 3 Months should be 50% (1 out of 2 clients).
Any suggestions are greatly appreciated. Thanks
