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.” -Superman1
Answers
-
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.” -Superman1 -
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.” -Superman0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 544 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 345 Distribute
- 87 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 8 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive