You'll want to create a simple table that contains all the possible 30 minute increments. You can then JOIN this table onto your data set to create your desired result. The reason for this is because Tableau can only create visualizations for rows that have underlying data. I.e. since there is no underlying data for 1:30 AM, it cannot be visualized.
Some exceptions exist, such as through using BINS, but I can't think of a way to use the BIN technique here.
Regardless, it's easy to create the table and it never needs to be updated. I created one for you which you can extract from the attached workbook.
You can use the calculation you already created to JOIN the tables together (keeping all records from the minute increment table--in this case a RIGHT JOIN).
Here's the output:
Here's a screenshot of the table
The Grand Total shows 0.33 because they are totaled using the AVERAGE of a zn(CountD(Case Date)). The ZN shows NULLs as 0s and allows us to take an average of all three case dates.
Hope that helps,
Tableau Example 210818 (1).twbx 21.9 KB
Oops, sorry about that.
I extracted the data and reuploaded to the original post.
Thanks for your quick response.
I can open the file, but cannot access the table you have created. Still asking me for login details to review data sources.
Unfortunately I cannot create the right join:
I have created my own simple excel 24Hrs file, but is not working.
Can you please share you tables?
24hrsTable.xlsx 8.5 KB
Yes, I've attached the table.
Most likely you couldn't do the RIGHT JOIN because the minute buckets were a DATE data type, and the calculation was a STRING data type, and the data types must match to do the join.
I took an additional step in the table to ensure the minute buckets were STRING data types--if you add an apostrophe in front of the time, it'll stay as a string and not convert to a date in excel. ( '12:00 AM instead of 12:00 AM -- note the apostrophe at the beginning).
Minute buckets.xlsx 4.1 KB
Thanks Ray for your quick turn around!
Indeed, I had a data type mismatch. Totally overlooked it.
Appreciate your help,