Fixed values from 1 to 24 (hours of day) // Currently I don’t know how to implement this
My dataset has a timestamp field, and I created a calculated field that extracts the hour from that field (Eg., YYYY-MM-DD 20:mm:ss → hour_of_day = 20) named hour_of_day
Expected
Visual in a vertical bar chart
X-axis: fixed value from 1 to 24
Y-axis: total records by grouping hour_of_day
Currently, I set hour_of_day to x-axis but data is log-like so hour_of_day might not enough value from 1~24
do I get you right. The issue is that not every hour is represented in your data and you want to make sure there is always every hour represented in the chart from 1-24?
One solution is to join a table that contains all hours from 1-24 (eg. in a csv file).
You need a simple table with each hour. Then you right join the table in your dataset using your calculated field and the column with the hours in your csv.
Best regards,
Nico
If this helped you, please mark my answer as solution. That helpes the community to find solutions faster.
@Nico I have tried CSV solution but my dataset is from an S3 datasource and I still don’t know how to use custom query to format timestamp to hour of day. I tried to join my record dataset with that csv dataset but my calculated field is not in joining field dropdown
@tobinguyen I guess you created the calculated field in the analysis. It is also possible to add a calc field In the dataset pane. Then you should find it as joining field.
You can come up with your 24 hours as fixed values and map your dataset values into each bucket from your dattaset values field using this video from @DylanM
@Giridhar.Prabhu I watched the video and tried it, but it doesn’t seem to work in my case (or maybe I did it wrong, sorry I’m very new to QuickSight). This is an example of my dataset, which only has two columns, and I need to count the number of userIDs at each time point during the day. In the case where there is only one record on that day, my rank would only be 1.
User ID
Logged At
1
2025-01-01 14:40:12
1
2025-01-02 14:40:12
1
2025-01-02 10:00:00
The chart should be: header is X-axis and row is values
Hi @DylanM Sorry for the late reply, I was busy and off for a while.
First, thanks for your video and response. But I have a few questions and would appreciate your help.
I see that in your example, you’re using denseRank and then grouping the results into three groups. In my case, if there are fewer than 24 records but I still need to display the full range from 1 to 24, how should I handle that?
Hi @tobinguyen,
It’s been awhile since last communication took place on this thread, did you have any additional questions or were you able to find a work around for your last question in the mean time?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Hi @tobinguyen,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
