Return Day with highest value - Case Statement

leeloo_dallas

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.

 

weekbymonth:

CONCAT(DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%b %d'),' - ',DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-7),'%b %d %Y'))

 

weekly_avg:

SUM((`video_plays`))
/
COUNT(DISTINCT DAYNAME(`date`))

 

avg_day:

(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)

 

MarkSnodgrass

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.

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Building_Each_Chart_Type/Calendar

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. 

jaeW_at_Onyx

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 @GrantSmith