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'