Return Day with highest value - Case Statement
Hey guys I have a date column from Oct-Dec, a numeric column of totals from the date, and finally a categorical date column full of names.
I've attached a sample data table.
I'm using a beastmode that splits my dates into weeks.
I want to create a case statement that returns the value of the day from the week that had the highest total. This way, we can know which day from that week was the most active.
CONCAT(DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%b %d'),' - ',DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-7),'%b %d %Y'))
(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'
I think you can avoid a lot of this using some of the built-in card functionality in Domo.
First, have you tried using the Calendar card? It is great for this type of request as it turns a calendar into a heat map and would quickly show you the highest days of the week.
If you are doing a bar chart or similar, I would create a simple beast mode of: DAYNAME('date') that returns the name of the day of the week. I would put this in the x-axis and then put your video plays in the y-axis and set the aggregation to average. Then, use the date range filter in the top-right corner of the card to group by month or week, or whatever you need.**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
If i'm understanding you correctly you want the week on the axis and then the day of the max total.
this is tough / not reallllllllly possible the way you want to without doing ETL (i don't think).
what you can do is SORT by RANK() -- such that the top ranked days of each week appear first, then the second best day of each week then the third etc.
to calculate RANK() you need window functions enabled (talk to your CSM) then set up the following beast mode:
sum(sum(1)) over (Partition By week_num order by sum(totals) desc)
You'll have to put week_num on the axis, as well as date and ideally the sum(totals) column
then sort by your RANK function ASC and date DESC
Then limit the number of rows displayed (in the data table at the bottom) to 10 or 15 to show the top date for the last n weeks.
cc @GrantSmithJae 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"1
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 209 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 85 Cards, Dashboards, Stories
- 18 Automate
- 355 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive