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.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1K Connectors
- 267 Workbench
- Cloud Amplifier
- 3 Federated
- 2.5K Transform
- 78 SQL DataFlows
- 524 Datasets
- 1.9K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 433 Beast Mode
- 22 Variables
- 508 Automate
- 114 Apps
- 386 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 331 Distribute
- 76 Domo Everywhere
- 255 Scheduled Reports
- 65 Manage
- 65 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 67 Community Announcements
- 4.8K Archive