Hour Buckets in Quicksight

[Gustavo_Gutierrez]

Hello Amazon Quicksight Community:

I have a recent problem trying to create Buckets of hours to my analysis, this is what I got:

The thing is, I’m in need of something like this:

I have a date column (YYYY-MM-DD 00:00:00), and Order_id column, I also did some SQL trying to show the date gaps, but my method I think is wrong, because what I’m doing is extracting the Hour of my date, and then visualize those numbers, but the thing is it can’t show all of the hours in the X axis, If You pay attention to the first image, I have missing numbers in the X axis, like the 3,4,5 AM. How can I Show all my sales hourly to know which are my dead business hours?. I mean visualize something like the second image that can visualize all my sales hourly.

Thanks for your help <3

[Sanjeeb2022]

Hi @Gustavo_Gutierrez - Is it possible to share sample data, just curious whether your data contain any hours for 2,3,4 or not.

Regards - Sanjeeb

[Gustavo_Gutierrez]

Hi @Sanjeeb2022, of course:

That’s the data I’m actually working with

[Gustavo_Gutierrez]

@Sanjeeb2022 The data that I’m working does not contain those hours, that’s why I’m trying to show those specific numbers otherwise the visualization has less value.

[Sanjeeb2022]

Hi @Gustavo_Gutierrez - I believe you extracted the hour from created timestamp? Am I correct?

Regards - Sanjeeb

[Gustavo_Gutierrez]

Yes @Sanjeeb2022 I did a extract(‘HH’,created_at) So I can extract the Hour Only of the created_at column but that is not a date to quicksight, So it can’t show gaps.

[Sanjeeb2022]

Hi @Gustavo_Gutierrez - The approach you are taking is correct ( I believe). Since in your data some of the hours fields are missing that is why those data points are missing. Let’s hear from experts on this.

Hi @eperts @ArunSanthosh - Can you please suggest here, in the input data we have some date fields with hours not all hours are available but want a continuous graph as explained above. What is the best way of doing it.

Regards - Sanjeeb

[eperts]

Hi @Gustavo_Gutierrez - This a very frequent challenge when plotting data over time, specifically when a calendar doesn’t exist and you are plotting the dates from your data.

I’m going to give you a solution, which works in your case since you need hourly grouping, but depending on your data it may not look pretty.

In my case I’m using SPICE and CSV files.

1. Create a DataSet and load your main data. Then create the field that extracts the hour. In my case that looked like this

2. Create a CSV file with the hours of the day in 24h format. In my case that looked like this

3. Create a second DataSet where we are going to bring in the CSV file and left join DataSet1 on the hour column. That looks like this

What we have now is the following data structure:

The first column is the hour column coming from the CSV file we created in step 2. This is the column that we will use in our analysis to plot the data for the following result

One last thing that needs to be set is sorting of the x axis so it’s done by hour

If this works for you please mark my reply as the solution so others can benefit.

[Gustavo_Gutierrez]

Thank you @eperts,

This is the real solution, the only thing that annoys me is that can’t be filtered by date, because the X axis will not show all the hours, but this is it! Thanks a lot eperts <3

[eperts]

Just to close the loop and answer your question. A more elegant way to achieve the same would be to create a calendar at hourly grain. That calendar can be in CSV format or stored in a database table. You only need to generate it once. That may be something like this

month,day,year,calendar_key
12,2023,12/01/2023T14:00:00

As you can see the calendar_key is the timestamp aggregated to the hour.

Then in your data instead of extracting the hour you can create that calendar_key field. That would allow you to filter by date.

[Sanjeeb2022]

Thank you @eperts . In data warehouse world, we always have a date dimension to tackle this kind of issues. Thanks for providing the solution, it just remind me my old days data modelling stuff.

Regards - Sanjeeb