How do I write the syntax for YOY % change?
Hi all,
I am trying to create a calculate field where I am able to see only the change from year to year in average purchase price.
I understand the math to be purchase price1 - purchase price 2 / purchase price 1 but I am not getting the correct output when I try the syntax
Here is what i have so far:
Any suggestions on how to write the correct syntax for this formula?
I have tried writing it a few different ways and keep getting syntax errors.
If this helps, feel free to agree, accept or awesome it!
Answers
-
You'll want to do something like:
( AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE()) THEN `purchase_price` END) - AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE())-1 THEN `purchase_price` END) ) / AVG(CASE WHEN YEAR(`purchase_date`) = YEAR(CURDATE())-1 THEN `purchase_price` END)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
So I think by using the CURDATE function in there its only wanting to give me the change in this specific year.
You can see at the bottom there is average income numbers filled in the 2018 year. How would I change that syntax to include those years in the visualization as well?
If this helps, feel free to agree, accept or awesome it!
0 -
What you'll want to do then since you want historical values you can configure a date dimension dataset to then compare the data to itself with a specific offset (in your case the year). I've done write ups on how to do this here: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith Hopefully this comes off the right way but is that the simplest way to do it?
If this helps, feel free to agree, accept or awesome it!
0 -
It may seem a bit complex but because you're wanting to get YoY for dates other than this year this is the best option.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Well, dang. Thanks for the link
If this helps, feel free to agree, accept or awesome it!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 60 App Studio
- 41 Variables
- 687 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive