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

user10673

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 !

 

 

 

ST_Superman

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)

ST_Superman

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

Many thanks ! you just saved my day!

ST_Superman

All in a day’s work