'Year Over Year' Card Ahead of Available Data
Hello,
I am pulling in sales data on a 4 day lag. I would like to plot data with year over year comparisons, but want the comparison period to match the data I have in the system, not the current date. Right now, my charts are comparing Sales Totals through May 8th (today) last year to only through May 4th this year (last available data I have), presenting results as worse than they are . Is there a way to make the year over year data presented only extend to the "Max" of my 'start date' field that indicates the latest day of data I have?
Best Answer
-
{Re-reading your original post.} You could find your most recent record's date using some kind of max date. But it seems you know the scheduling delay already as somewhere around 4 days.
Yes…as added columns.
My comment shows how I do this kind of thing dynamically. The formula is saying….take the sum of all the records that occur last year, and whose date is not greater than one year prior to the current date.
The part showing : <= DATE_ADD(CURRENT_DATE(),-365)
…is the limit. It prevent days beyond one year ago today. Just increase that by 4 (to -369) to limit to four days earlier. But you will also do a formula for CY and make sure it goes back 4 days as well…even if your data won't.
When I do this, I will have this year and last year fields. And sometimes even years before those. My abbreviations stand for month-to-date, previous-month-to-date, quarter-to-date, and year-to-date.And my columns will look something like this…
Doing this I can show dashboards comparing year over year, month over previous month, quarter over previous quarter, etc.
Its not perfect, because the number of business days won't match perfect unless you really refine your logic. But its serves my need and might serve yours.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
I do this using calculated fields that represent this year and last year. So Last Year YTD would be something like this…
sum(
case when YEAR(transaction_date
) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
andtransaction_date
<= DATE_ADD(CURRENT_DATE(),-365)
thenamount
else 0 end
)
And obviously This Year YTD wouldn't have the -365 date add.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose thank you! Do I do this as an added column? Can you provide some more detail on where I use this formula to make it usable at the card level?
0 -
{Re-reading your original post.} You could find your most recent record's date using some kind of max date. But it seems you know the scheduling delay already as somewhere around 4 days.
Yes…as added columns.
My comment shows how I do this kind of thing dynamically. The formula is saying….take the sum of all the records that occur last year, and whose date is not greater than one year prior to the current date.
The part showing : <= DATE_ADD(CURRENT_DATE(),-365)
…is the limit. It prevent days beyond one year ago today. Just increase that by 4 (to -369) to limit to four days earlier. But you will also do a formula for CY and make sure it goes back 4 days as well…even if your data won't.
When I do this, I will have this year and last year fields. And sometimes even years before those. My abbreviations stand for month-to-date, previous-month-to-date, quarter-to-date, and year-to-date.And my columns will look something like this…
Doing this I can show dashboards comparing year over year, month over previous month, quarter over previous quarter, etc.
Its not perfect, because the number of business days won't match perfect unless you really refine your logic. But its serves my need and might serve yours.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive