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
-
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)
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________1
Answers
-
Many thanks ! you just saved my day!
0 -
All in a day’s work
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 104 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有