beast mode formula: Previous week compare to same week last year

user10673
user10673 Member
in Magic ETL

Hi, 

 

I am struggle to get a beast mode formula to work for calculating the sum of same week last year,

our business date start from Sun- Sat, i want to get the formula to only calculate previous week data

e.g. today is 24th Wed but i want to calculate the week from 14th Sun -20th Sat 

DOMO says the formula for LY is validated but it is showing the same number as previous week this year.

 

Assuming this formula is correct:

Previous whole week this year 

SUM(CASE WHEN YEARWEEK(` Date`,0) >= YEARWEEK(CURDATE(),0)-1 AND YEARWEEK(`Date`,0) < YEARWEEK(CURDATE(),0) THEN ('Rev') END)

 

Previous whole week last week (this does not work)

SUM(CASE WHEN YEARWEEK(`Sales Date`,0) >= YEARWEEK(CURDATE(),0)-1 AND YEARWEEK(`Sales Date`,0) < YEARWEEK(CURDATE(),interval 1 year) THEN ('Rev') END)

 

 

Many thanks !

 

 

 

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    I would make a few changes to your beastmodes.

    Previous whole week:

    SUM(CASE 
    WHEN YEARWEEK(`Date`,0) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK),0)  
    THEN ('Rev') 
    ELSE 0
    END)

    By using date_sub you will prevent errors when you are on week 1 of a year.

     

    Previous whole week from last year:

    SUM(CASE 
    WHEN YEARWEEK(`Date`,0) = YEARWEEK(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 1 WEEK),0)  
    THEN ('Rev') 
    ELSE 0
    END)

Answers

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    I would make a few changes to your beastmodes.

    Previous whole week:

    SUM(CASE 
    WHEN YEARWEEK(`Date`,0) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK),0)  
    THEN ('Rev') 
    ELSE 0
    END)

    By using date_sub you will prevent errors when you are on week 1 of a year.

     

    Previous whole week from last year:

    SUM(CASE 
    WHEN YEARWEEK(`Date`,0) = YEARWEEK(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 1 WEEK),0)  
    THEN ('Rev') 
    ELSE 0
    END)
  • user10673
    user10673 Member

    Many thanks ! you just saved my day!

  • ST_Superman
    ST_Superman Domo Employee

    All in a day’s workAll in a day’s work

     

  • Engineer_0
    Engineer_0 Member
    edited January 9

    @ST_Superman - I'm using YEARWEEK() in MagicETL and BeastMode to identify the previous week (Sunday-Saturday). The function in MagicETL works as expected; it allows the YearWeek value to cross over the year. However, the BeastMode version does not work correctly, counting more weeks than it should in 2024. I've submitted this to support (case: 05895315), but I'm still awaiting a resolution.

    BeastMode Bug - YEARWEEK().png

Categories