I’m new to QuickSight and data analysis in general so still learning the kinks. I was wondering if the community could help me with a problem.
Imagine I have 2 tables of data which I have joined using the Case ID field.
Case ID | Sender name | Message timestamp
123XYZ| bob | 11/12/2023 07:48
123XYZ| sarah |12/12/2023 12:45
123XYZ| julie |12/12/2023 15:32
Case ID | All people involved in the case
123XYZ | bob
123XYZ | sarah
123XYZ | julie
123XYZ | joe
123XYZ | holly
123XYZ | megan
123XYZ | ben
123XYZ | beth
123XYZ | nick
What this is telling me is that for case 123XYZ, there were 9 people involved in the case, but only 3 people actually ever provided a message to the conversation - Bob, Sarah and Julie. All the other people had visibility but never sent a message.
Now, in my analysis, I want to visualise this stat of “number of people who were involved in a case but never actually contributed” for every single case ID. I’d like to use a horizontal bar chart where the Y axis is “number of people who didn’t contribute” and X axis is the count of how many cases for that number e.g. there were 24 cases where 6 people didn’t contribute, 5 cases where 8 people didn’t contribute etc.
I figured it would be some calculated field but I don’t know what functions to use.
Any help would be great, thanks!