beast mode for total active tickets per hour

AbeB · May 16

Hi Everyone,

I was hoping to get some assistance on writing a beast mode for getting a table to show total number of tickets per hour. Something like the one below:

I have something like this so far-

DATE_FORMAT(Date_Entered_UTC, '%Y-%m-%d %H:00:00'), COUNT(TicketNbr)

but getting an error when trying to submit it.

Any assistance is greatly appreciated.

ArborRose · May 20

An hour bucket is a way of grouping timestamps by the hour they fall into. So for example:

`Date_Entered_UTC`	Hour Bucket
2025-05-20 08:23:17	2025-05-20 08:00:00
2025-05-20 08:55:42	2025-05-20 08:00:00
2025-05-20 07:05:10	2025-05-20 07:00:00

It's saying "group everything that occurred between 8:00 and 8:59:59 under the bucket for 8:00. You have this in your formula. You are doing it in your screenshot. You are counting tickets grouped by that hour.

DATE_FORMAT(`Date_Entered_UTC`, '%Y-%m-%d %H:00:00')

If you want to show them by the hour only (8:00, 9:00, 10:00, etc), you could try

DATE_FORMAT(`Date_Entered_UTC`, '%H:00')

Oh, by the way…I would separate date as a column formatted as short date such as 5/20/2025 and hour as another column. Then put each in the sort to arrange them ascending or descending and include maybe year and month columns if this chart spans a long period.

ArborRose · May 16

Create a beast mode formula (calculated field) using something like Hour Bucket as

DATE_FORMAT(`Date_Entered_UTC`, '%Y-%m-%d %H:00:00')

Then you want to create a card with a table that aggregates using COUNT. Drop Hour Bucket on the card and then TicketNbr on the card and set it to COUNT.

COUNT(`TicketNbr`)

This will count the number of tickets for each hour bucket. Let me know if you need actual screenshots and I'll build a sample.

AbeB · May 20

@ArborRose Thank you for the assistance!

Can you clarify by what you mean "drop hour bucket" please?

This is what I have currently.

ArborRose · May 20

An hour bucket is a way of grouping timestamps by the hour they fall into. So for example:

`Date_Entered_UTC`	Hour Bucket
2025-05-20 08:23:17	2025-05-20 08:00:00
2025-05-20 08:55:42	2025-05-20 08:00:00
2025-05-20 07:05:10	2025-05-20 07:00:00

It's saying "group everything that occurred between 8:00 and 8:59:59 under the bucket for 8:00. You have this in your formula. You are doing it in your screenshot. You are counting tickets grouped by that hour.

DATE_FORMAT(`Date_Entered_UTC`, '%Y-%m-%d %H:00:00')

If you want to show them by the hour only (8:00, 9:00, 10:00, etc), you could try

DATE_FORMAT(`Date_Entered_UTC`, '%H:00')

Oh, by the way…I would separate date as a column formatted as short date such as 5/20/2025 and hour as another column. Then put each in the sort to arrange them ascending or descending and include maybe year and month columns if this chart spans a long period.

AbeB · May 20

@ArborRose

Thank you! This is exactly what I was looking for!

Have a great week!

beast mode for total active tickets per hour

Best Answer

Answers

Categories