YTD, YOY, M-1
Hi All,
I want to create a column for YTD, YOY comparison by month and M-1.
Example output is below: I have datetime in a format at yyyy-mm-dd
Can someone show a use case for this?
Best Answer
-
To compare month to a previous year's same month:
sum(
case when YEAR(`transaction_entry_date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and MONTH(`transaction_entry_date`) = MONTH(DATE_ADD(CURRENT_DATE(),-365))
and `date` <= DATE_ADD(CURRENT_DATE(),-365)
then `amount` else 0 endAnd add a criteria such as Month(date)=1 if you need to specify a month like January.
For a percentage, I create a formula to find the difference of CY and PY. That's just subtracting one from the other. Then use that as the numerator. So the percent would be something like CY-PY/PY.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
If you use formulas for dynamic years in an ETL using the Group By Tile, such as:
current year:sum(
case when YEAR(date
) = YEAR(CURRENT_DATE())
anddate
<= CURRENT_DATE()
andsku
='Product A'
thenamount
else 0 end
)previous year:
sum(
case when YEAR(date
) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
anddate
<= DATE_ADD(CURRENT_DATE(),-365)
andsku
='Product A'
thenamount
else 0 end
)previous 2 year:
sum(
case when YEAR(date
) = YEAR(DATE_ADD(CURRENT_DATE(),-730))
anddate
<= DATE_ADD(CURRENT_DATE(),-730)
andsku
='Product A'
thenamount
else 0 end
)
You can also use YEAR(`date`)=YEAR(CUR_DATE())-1. On the current year you don't need the second line. On previous years you do - to compare YTD to previous years YTD.
You can put it into a grouped bar chart with the property set to include the data table. Use an integer value for month to place on the sort to get the months to display in the proper order.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Oh…and add the condition of MONTH in the same way we do YEAR to get Month over Month.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose Hi thank you for helping
but I also want to see %YOY growth too
For ie. Jan 2024 compare to Jan 2023 = %YOY on Jan
And so on.. %YOY on Feb
Do you know how to do this?
0 -
To compare month to a previous year's same month:
sum(
case when YEAR(`transaction_entry_date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and MONTH(`transaction_entry_date`) = MONTH(DATE_ADD(CURRENT_DATE(),-365))
and `date` <= DATE_ADD(CURRENT_DATE(),-365)
then `amount` else 0 endAnd add a criteria such as Month(date)=1 if you need to specify a month like January.
For a percentage, I create a formula to find the difference of CY and PY. That's just subtracting one from the other. Then use that as the numerator. So the percent would be something like CY-PY/PY.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **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
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 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