Create Chart by Hour of the Day in Bubble

[heyshivamkr]

I have a purchase database which contains the following data:

1. Date
2. Purchase
3. Hour of the day (I am using the extract formula in my workflow to extract the hour (0-23) from the date field and storing it as a number).

Now, I want to create a vertical chart that shows the number of purchase counts for each hour of the day:

For this, on the Y-axis, I am doing a search for “purchase,” grouping the data by the hour of the day field. The type of grouping is exact, and the aggregation is ‘count.’

For the X-axis, I am again doing a search for “purchase,” grouping the data by the hour of the day field. The type of grouping is exact, and the aggregation is ‘count.’ This time, I am plotting the hours of the day formatted as numbers.

This is creating this kind of chart.

I want two improvements in this chart.

Firstly, I don’t want it to show only those hours when there is a purchase; instead, it should show all 24 hours.

Secondly, the hour should be formatted as 1 AM, 2 AM, 3 AM, 4 AM, and so on.

Can somebody please help me with this?

[Jett]

You should generate a list of numbers 0-23 and use that for your X-axis. You can use a plugin or JS to do that.

Depending on what you are using to create the graph, that may not even be necessary, as some graph elements have the option to exclude/include empty values (aka hours for which there are no purchases.)

Btw, you don’t really need to extract the hour from the date and save it separately. You can probably group on the date/time field directly. That also solves your formatting problem, which would just give you all the date/time formatting options that are already available.

[georgecollier]

Check ‘do not skip empty groups’ in the group by operator.

[heyshivamkr]

Hi, the problem is I don’t see any option to group by hour. It is either day or month. Am I missing something here?

[heyshivamkr]

Hi George,

If i am checking that option. It is asking for ending value. What should i put here?

[georgecollier]

The final value of the graph, probably 23.

[heyshivamkr]

No it didn’t make any difference.

[georgecollier]

You probably haven’t done it for both searches.

[Jett]

Try group by day, then each item day: extract hour, group by hour

[heyshivamkr]

I have done it for both:

[georgecollier]

Is hour of the day a text or number?

[heyshivamkr]

Is this for X axis or Y Axis. Can you please elaborate this. I am new to bubble.

First i should do a search for purchase, group by date with an interval of 1 day and then extract hour. After this i am not able to group again.

[heyshivamkr]

Number

[Jett]

The chart is for a specific day, right?
So search for purchases (where date is specific date): group by hour: count

[heyshivamkr]

No, it is for all the day combined.

[Jett]

well yes, but one at a time, no?

[joqr231210]

Maybe you can:

Create an OS for hours, 1 to 24

When the thing is created, assign the hour in the thing, using the OS.

Then in the chart group it by hour (OS) filtered by the date you want to see…

[heyshivamkr]

Hi, I have created an option set with value starting from 00 AM to 11 PM

Then in the Purchase database, I added a field of email hour which is linked with the option set.

However, I am not able to figure out how can I extract the hour from my date and automatically assign relevant options from option sets.

Can you please guide me here?

[joqr231210]

Actually,

With the option set you should set the field:
Get an option > all Purchase hours > filtered by > This Purchase hour Display converted to number is Input Date Time extract hour…

But thinking about it, I don’t remember why I suggested the Option Set… it could really be done with a number field, and just saving it with the Date vale > extract hour….

[heyshivamkr]

Hi,

In the first approach, i am getting stuck here.

I had already tried the second approach, where I just extracted the hour number and stored it as a number field. However, I encountered two issues when plotting it on the graph:

1. I am unable to display all hours on the chart; it only shows the hours that are stored in the database.
2. On the X-axis, I want to display times like “1 AM,” “2 AM,” “3 AM,” “4 AM,” and so on. However, with just the number field, it displays as “1,” “2,” “3,” “4,” “5,” etc.