Return AVG DayName
Hey I was wondering if there is a beastmode windows function that would allow me to return the avg dayname from the average of x?
I have data that includes a domain, a column for day name(made via beastmode), and an avg of impressions).
Any ideas? I thought the following would work if I added concat in front of it but I keep getting 0 as a returned response.
avg(distinct DAYNAME(date))
Best Answer
-
Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:
CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:
CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Oh my gosh I didn't even think to use DAYOFWEEK.
It works well too!
I was curious, do you think this returns there's a way to FLOOR or CEILING to get the lowest avg and highest avg to DAYOFWEEK?
0 -
Kind of like this?
(CASE ROUND(AVG(FLOOR(DAYOFWEEK(`date`))),0) WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END)0 -
Yes, I thought about including floor or ceiling in my reply, but wasn't sure which way you would want to go. I would do some testing and just see what works best for your situation.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Will do! Thanks so much for your help!
Also, did I apply that correctly? Like the FLOOR to the query?
0 -
Actually FLOOR would replace ROUND and it would be outside the average function. Like this:
FLOOR(AVG(DAYOFWEEK(`date`)))
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Here's link describing the floor and ceiling functions.
https://www.mathsisfun.com/sets/function-floor-ceiling.html
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
... oof are you trying to figure out on which day of the week people tend to visit your site?
if yes, then you should definitely bake day of week into your dataset via ETL, different implementations of dayofweek will shift Monday from 1 to 0, and that's not the sort of thing you want to guess about.
meanwhile, it's interesting you mention avg. number of impressions, b/c depending on the granularity of your dataset (what each row of data represents), the avg. number of impressions may differ from the avg day of week in your dataset.
make sure to be sure the math you're calculating is answering the business question!
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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive