'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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 544 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 345 Distribute
- 87 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 9 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive