Beast Mode Help

Ashleigh

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.

MarkSnodgrass

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

GrantSmith

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

MarkSnodgrass

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

Ashleigh

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