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
- 10.5K All Categories
- 4 Connect
- 914 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive