Grouping by the Hour and alerting based on count of usage per hour.
Hello,
I am trying to do a group by using hour of day. The date operator in Magic does not have that funcationality but curious how I can do it within magic or in SQL. Here is my data where I want to count how many times a code was use in an hour for each day I want to use.
COUPON-CODE | updated_at | Order # | Goal(Count of Usage by Hour) |
10% | 17-Apr-20 10:04:53 | 1 | 2 |
10% | 17-Apr-20 10:43:49 | 2 | 2 |
10% | 17-Apr-20 5:43:25 | 3 | 1 |
10% | 17-Apr-20 0:46:53 | 4 | 1 |
10% | 17-Apr-20 17:10:14 | 5 | 1 |
20% | 17-Apr-20 2:51:38 | 6 | 1 |
20% | 17-Apr-20 6:42:59 | 7 | 1 |
20% | 17-Apr-20 13:24:49 | 8 | 2 |
20% | 17-Apr-20 13:53:14 | 9 | 2 |
50% | 17-Apr-20 15:51:46 | 10 | 1 |
50% | 17-Apr-20 16:02:26 | 11 | 3 |
50% | 17-Apr-20 16:27:03 | 12 | 3 |
50% | 17-Apr-20 16:27:03 | 13 | 3 |
Best Answer
-
In Magic: create a copy of the column converted to TEXT and do a REGEX / STRING transform.
In MySQL: use date_part functions (like hour).
Best Solution: split date and time (HH:mm) into separate columns during data ingestion. THEN use a Fusion to join a Time Dimension table (where you have one row for every minute of the day).
Why is Fusion the best model?
Because it minimizes the number of times you have to read and write the data. AND by using a Time dimension you can create custom groupings like ("Morning", "coffee-break", "evening") without using a complex beast mode in each of your cards.
Why does splitting date_time make sense?
Because you'll want to see a trend in behavior on Saturdays versus trend in behavior on Mondays. By using a Time dimension you can avoid ugly beast modes.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
Do you have the time stamp for the hours you're wanting to graph? Domo now supports hour granularity in the time slicer so you could graph by hour. You could also possibly use the HOUR function in a beast mode might work as well
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hello,
It just won't work for the alert. You have to group it to give it a trigger.
0 -
What are you attempting to alert on?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
In Magic: create a copy of the column converted to TEXT and do a REGEX / STRING transform.
In MySQL: use date_part functions (like hour).
Best Solution: split date and time (HH:mm) into separate columns during data ingestion. THEN use a Fusion to join a Time Dimension table (where you have one row for every minute of the day).
Why is Fusion the best model?
Because it minimizes the number of times you have to read and write the data. AND by using a Time dimension you can create custom groupings like ("Morning", "coffee-break", "evening") without using a complex beast mode in each of your cards.
Why does splitting date_time make sense?
Because you'll want to see a trend in behavior on Saturdays versus trend in behavior on Mondays. By using a Time dimension you can avoid ugly beast modes.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
Hi,
Trying to alert anytime I have a code used more than 3 times an hour.
0 -
I know this doesn't help you now, but given that you and several other users voiced challenges dealing with date and time data, i'm going to run a mini-hackathon on handling date and time in Domo next week thursday.
You're welcome to join: https://dojo.domo.com/t5/EMEA/DUG-Meeting-30-40-2020-1430-UTC-BUILD-IT-Working-with-DateTime/m-p/48274#M11
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive