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 !

Find more posts tagged with

Accepted answers

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)

All comments

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

Engineer_0

@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.