Calculate new field using 2 tables

Hi all,

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!

Hi @bjscola1
the great thing about QS is you can easily play around and see how to arrange you fields within the bar chart.

First you could just use a filter to exclude all rows with an timestamp.
Second you can just use the “name” as a value and let QS do the count automatically.
Third you can move case and name into x or y axis to see if it will bring you the right bar chart.

BR

1 Like

Thanks Erik! I think I am almost there although I am struggling with the filter. Which field would I filter to achieve this? As when filtering the timestamp field I am only able to choose date ranges etc. as it is a date field type

You could create a new toString(timestamp) field and use that for the filter

1 Like

hi @bjscola1,

did the solution provided by @ErikG help you solve your question? let us know. if so, please help us mark it as Solution.

kind regards,
Wakana

1 Like

Hello @bjscola1 !

It has been a few days since we have heard from you, but would still like to help. If we do not hear from you in 3 days this post will be archived.