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
- 7.3K All Categories
- 13 Getting Started in the Community
- 134 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 52 Domo Everywhere
- 2K Charting
- 994 Ideas Exchange
- 894 Connectors
- 236 Workbench
- 342 APIs
- 76 Apps
- 18 Governance & Productivity
- 233 Use Cases & Best Practices
- 49 News
- 473 Onboarding
- 570 日本支部