YOY calculations - DOMO recommended formulas not working
I found this page for doing period-over-period calculations:
However, the one I really want is a YoY calculation for the same series, but when I try that formula, it comes back invalid:
DOMO YoY Calculation
Case year(`Datefield`) when year(curdate()) then 'This Year' when year(curdate())-1 then 'Last Year’ End
Updated for my data:
Case year(`FULL_DATE`) when year(curdate()) then 'This Year' when year(curdate())-1 then 'Last Year' End
Two things I noted:
1.) There is a single quote in the original formula that doesn't get recognized properly by Beast Mode (the last single quote).
2.) I have never seen a Case When statement where a criteria was called between the Case and When. I am relatively new to this, so it might just be my lack of experience.
Any help would be appreciated!
Thank you,
AD
Best Answer
-
Hi,
Try it like this:
Case When year(`FULL_DATE`) = year(curdate()) then 'This Year'
When year(`FULL_DATE`) = year(curdate())-1 then 'Last Year' End
Hope it helps.
Ricardo Granada
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.1
Answers
-
Here's an example of a beast mode I am using to calculate sales PYTD:
sum(
case when `Invoice Date` <= DATE_SUB(DATE_SUB(CURDATE(), interval (DAYOFWEEK(CURDATE())) day), interval 1 year)
and YEAR(`Invoice Date`) = YEAR(DATE_SUB(DATE_SUB(CURDATE(), interval (DAYOFWEEK(CURDATE())) day), interval 1 year))
then `Extended Price` else 0
end)
Notice that the case statement is nested within a sum statement. Ultimately that's what I'm needing to do, provide a sales total for prior year at the same point in time.
0 -
Hi,
Try it like this:
Case When year(`FULL_DATE`) = year(curdate()) then 'This Year'
When year(`FULL_DATE`) = year(curdate())-1 then 'Last Year' End
Hope it helps.
Ricardo Granada
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.1
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive