Beast Mode Help

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.

**If this answer solved your problem be sure to like it and accept it as a solution!

Tagged:

Best Answer

  • MarkSnodgrass
    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.

    **Check out my Domo Tips & Tricks Videos

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

Answers

  • 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!**
  • MarkSnodgrass
    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @MarkSnodgrass Thanks, this worked!!! My brain was hurting trying to figure this one out lol

    **If this answer solved your problem be sure to like it and accept it as a solution!