'Year Over Year' Card Ahead of Available Data

ch2420

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?

ArborRose

{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.

ArborRose

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))
and transaction_date <= DATE_ADD(CURRENT_DATE(),-365)
then amount else 0 end
)

And obviously This Year YTD wouldn't have the -365 date add.

ch2420

@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?

ArborRose

{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.