Last 4 weeks accumulated beast mode

user06363

Hello,

 

I'm trying to write a beast modes that shows the last 4 weeks as Last Week, the previous 4 weeks before that as Previous Week, and the last 4 weeks prior year as Prior Year.

 

The reason why I will create 3 beast modes is because  only want 3 columns showing for these accumulated week periods, versus showing 52 columns total (weekly column)

 

The follow is a beast mode for last week:

 

SUM(CASE WHEN (
DATE((CASE WHEN DAYOFWEEK(`SentDate`) = 1 THEN `SentDate`
WHEN DAYOFWEEK(`SentDate`) > 1 THEN ADDDATE(`SentDate`, 8-DAYOFWEEK(`SentDate`))
END)) =

DATE(DATE_SUB((CASE WHEN DAYOFWEEK(CURDATE()) = 1 THEN CURDATE()
WHEN DAYOFWEEK(CURDATE()) > 1 THEN ADDDATE(CURDATE(), 8-DAYOFWEEK(CURDATE()))
END), INTERVAL 1 WEEK))
)
THEN `NumberDelivered` END)

 

Can anyone advise?

 

Thank you,

Summer

user06363

Thank you for responding!

 

I've found a solution, 

For last 4 weeks:

CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-4 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END

 

For last 8 weeks:

((CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-8 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END)

-

(CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-4 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END))