YTD vs Year Prior
Hi guys,
Looked in other posts here but couldn't find the exact answer. I'm creating a table card that would show YTD vs Year prior (Bar charts are not an option since comparing many different categories. The best I could find to show Year prior is
SUM(CASE WHEN YEAR(DocDate
)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND month(DocDate
) <= month(CURDATE()) THEN DocTotal_Contribution
ELSE 0 END)
The issue with that is that is is summarizing all data from 2023 including all month of April. I want to compare data from time Jan 01 2023 till today (April 18 of 2023). Vs Year to Date (Jan 01 2024 vs today)
To calculate YTD I use the following formula and is working just fine.
SUM(CASE WHEN YEAR(DocDate
) = YEAR(CURDATE()) THEN DocTotal_Contribution
ELSE 0 END)
How can I add the extra layer to count also the current day in the year prior?
Thanks
Answers
-
That's the formula I use. The version you show is not summing the full year. That month condition means it is summing YTD (year to date)…..less than or equal to the current month. You need to put some more criteria to it for the period you want. I am about to go to a meeting, I'll try to give you something specific when I can.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
The condition "<= month(CURDATE())" means compare years up until today's date. It is comparing Jan through today (April 18, 2024). For specific ranges like a specific month:
To include records where the month is April -
SUM(CASE WHEN YEAR(DocDate) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
AND MONTH(DocDate) = 4
THEN DocTotal_Contribution
ELSE 0 END)** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Oh…make it less than but not equal if you want up to today but not including today.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose thank for your help. I tried your syntaxes but they all seem to be including full April for 2023.
This syntax
SUM(CASE WHEN YEAR(
DocDate
)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND month(DocDate
) <= MONTH(CURDATE()) THenDocTotal_Contribution
ELSE 0 END)still returns data for 2023 including full month of April. I think the logic in the syntax above includes all data for year prior PRIOR to the end of April 2023. Any way to change this logic to show only until 18? It will be a live card so I don't want to use specific numbers so it the card the update with time.
0 -
@ArborRose this is the formula I just used and it returns what I need
SUM(CASE WHEN
DocDate
<= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) ANDDocDate
>= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR), '%Y-01-01')
THENDocTotal_Contribution
END)Thanks
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive