Beast Mode Help

Ashleigh
Ashleigh Florida 🟣
in Card Building

Help, my brain is stuck. I am trying to come up with a BM that will filter my data. I have an orders dataset and for some reason someone chose Wednesday as their reporting day. I want to show that if the day is Wednesday then we will show orders from the current Wednesday to the past Wednesday. But if we have not reached Wednesday yet then we should the previous Wednesday to the Wednesday before. Filed is just an order date. I am trying to use some combination of day of week functions and cur date but having some trouble.

So as of today I would show orders from 3/9-3/16 but tomorrow it will switch to 3/16-3/23.

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    @Ashleigh I believe this will work for you:

    /* check to see if today is Wednesday */
CASE WHEN DAYOFWEEK(CURRENT_DATE()) = 4 THEN
/*its Wednesday, so just show the last 7 days */
	CASE WHEN `date` >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) AND `date` <= CURRENT_DATE() THEN 'Include'
  ELSE 'Exclude'
  END
WHEN DAYOFWEEK(CURRENT_DATE()) > 4 THEN
/* its Thursday or later, so just show the most recent week */
	CASE WHEN `date` >= DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY)
  AND `date` <= DATE_SUB(CURRENT_DATE(), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY) THEN 'Include'
  ELSE 'Exclude'
  END
ELSE
/* its not Wednesday yet, go back an extra week */
	CASE WHEN `date` >= DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY) 
  AND `date` <= DATE_ADD(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK), INTERVAL (4 - DAYOFWEEK(CURRENT_DATE())) DAY) THEN 'Include'
  ELSE 'Exclude' 
  END
END


    couldn't easily test since I was using current_date, but I think should work for you.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    You can use a CASE Statement and DAYOFWEEK to check if it's before Wednesday and then configure and setup your date filters

    CASE WHEN DAYOFWEEK(CURRENT_DATE()) <=4 THEN offset logic here END


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Ashleigh
    Ashleigh Florida 🟣

    @MarkSnodgrass Thanks, this worked!!! My brain was hurting trying to figure this one out lol

Categories