How to add a calendar day as x-axis for showing daily incoming and closed bar chart

Question & Answer
,
1

Hello, I have a bar chart with y-axis showing the daily “incoming” and “closed” cases, is there a way to set x-axis as calendar day, then let y-axis to count the sum of created and closed cases from data table, example data:

Column 1 Column 2 Column 3 Column 4
case id status created time closed time
1111 open 2024-01-01
2222 closed 2024-01-01 2024-01-02
3333 closed 2024-01-02 2024-01-03
4444 closed 2024-01-04 2024-01-05
5555 closed 2024-01-05 2024-01-05

How to show the bar chart data as below:

Column 1 Column 2 Column 3 Column 4
date created case closed case
2024-01-01 2 0
2024-01-02 1 1
2024-01-03 0 1
2024-01-04 1 0
2024-01-05 1 2

Thanks.

2

Hello @qianglon , welcome to the Quicksight community!

To do this you could try the following:

created_cases = distinct_countIf({case_id}, {status} <> NULL)
closed_cases = distinct_countIf({case_id}, {status} = 'closed')

Then you can add those to your value field well for the bar chart, and your date field to the x-axis field well.

Let me know if this helps!

3

Hello duncan@, thank you for reply!! In the database there is no “calendar date” field, only “created time” and “closed time”, for example, in above example data, there is no 2024-01-03 in “created time” column, and no 2024-01-01 in “closed time” column. So the questions is how to create/add a “calendar date” field as x-axis and link to the sum of created_cases and closed_cases.

4

Hey @qianglon !

Can you show me exactly what your created_time and closed_time fields return in your table? Quicksight doesn’t have the ability to generate a date field for your data.

You could try plugging your problem into Arena so that I can take a look at what you are seeing.

5

Hello @qianglon !

Were you able to find a resolution to this problem or do you still need help? Again, Quicksight doesn’t have the ability to generate a date field for your data.

6

Hi @duncan, Thanks so much for your following. I created a table on Arena, see if there’s a way to have a bar chart for showing the sum of created case and closed case at the calendar day (x-axis).