Last 4 weeks accumulated beast mode

user06363
user06363
in Magic ETL

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

Best Answer

  • user06363
    user06363
    Answer ✓

    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))

     

Answers

  • Property_Ninja
    Property_Ninja

    Hi Summer,

     

    Can you just do something like ... 

     

    CASE WHEN SentDate >= current_date - interval 28 day then 'Last Week'
    WHEN SentDate >= current_date - interval 56 day and SentDate < current_date - interval 28 day then 'Previous Week'
    WHEN SentDate >= DATE_SUB((DATE_SUB(current_date, INTERVAL 1 year)), INTERVAL 28 DAY) and SentDate <= current_date - interval 1 year as 'Prior Year' END

    Then this would be your x axis and NumberDelivered could be your Y Axis

     

    Hope this helps,

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • user06363
    user06363
    Answer ✓

    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))

     

Categories