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

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-_
    Accepted 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