Say I have data for 2013 for the entire year and for 2014 through today and I want to see total amounts for 2013 through the same day/month for both years.

Example

2014
Jan $100,000
Feb
Mar

2013
Jan $60,000
Feb $90,000
Mar $80,000

I want to see a YTD total for 2014 of $100,000 and YTD total for 2013 of $60,000 through same day/month.

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Teri,

your example is unfortunately a bit spare concerning what you really have in your data-source.

I mean -

2014
Jan $100.000
...

Well, do you have a data-column "Month" that only contains the month (not the year, and the year is somewhere else)?

If you have a column that contains something like HMYY - then you can create a define like

 
DEFINE FILE YOUR_TABLE
  D_YEAR/I4 = HPART(YOUR_MONTH_YEAR_FIELD, 'YEAR', D_YEAR);
END

Then you should be able to BY or TOTAL over D_YEAR.

Hope that's what you are looking for

Cheers Linne

This is how we calculate prior year YTD:

-SET &CURR_YEAR = EDIT(&YYMD , '9999');
-SET &CURR_MONTH = EDIT(&YYMD , '$$$$99');
-SET &CURR_DAY = EDIT(&YYMD , '$$$$$$99');
-SET &LAST_YEAR = &CURR_YEAR - 1;
-SET &PR_END = &CURR_MONTH || '/' || &CURR_DAY || '/' || &LAST_YEAR;

I try to keep the dates that I use in my WHERE clause as close as possible to the format in the date.

In the example below that is date/time in the FULL field.

The result in &Earlier_Year is exactly one year earlier than today's date and that in &SIXMTHBACKDATE is 6 months earlier.

Hopefully I didn't hack this up to much while creating the example.

-SET &SERVERDATE = &YYMD;
-SET &Earlier_Year = DATECVT((DATEADD((DATECVT(&SERVERDATE,'I8YYMD','YYMD')),'Y',-1)), 'YYMD', 'I8YYMD');
-SET &ReportYear = EDIT(&Earlier_Year,'9999$$$$');
-SET &ENDDATE = EDIT(&SERVERDATE,'9999/99/99');
-SET &SIXMTHBACKDATE = EDIT(DATECVT((DATEADD((DATECVT(&SERVERDATE ,'I8YYMD','YYMD')),'M',-6)), 'YYMD', 'I8YYMD'),'9999/99/99');
-SET &STARTDATE = EDIT(&SERVERDATE,'9999/01/01');
...
DEFINE DATA
DOCUMENT_DATE_ONLY/YYMD = HDATE(DOCUMENT_DATE__FULL,'YYMD');
END
...
WHERE FISCAL_YEAR GE '&ReportYear.EVAL'
WHERE DOCUMENT_DATE_FULL GE DT(&Earlier_Year 00:00:00.000)
WHERE DOCUMENT_DATE_FULL LE DT(&ENDDATE 12:59:59.999)
...
COMPUTE 6MO_QTY = IF DOCUMENT_DATE_ONLY DT(&SIXMTHBACKDATE.EVAL) AND BLDAT LT DT(&ENDDATE.EVAL) THEN RECNT_QTY ELSE 0;

Right after I posted I see and error. GE and LE in the compare

COMPUTE 6MO_QTY = IF DOCUMENT_DATE_ONLY GE DT(&SIXMTHBACKDATE.EVAL) AND BLDAT LE DT(&ENDDATE.EVAL) THEN RECNT_QTY ELSE 0;

Convert to I8yyyymmdd and substract 10000, then convert back.

i.m.o. simplest way,

greets

Not so fast. What will happen when the current date is Leap Day? Make sure your solution won't insert a nonexistent date (like '2015-02-29') in the WHERE clause.

leap years
faites attention

I was wondering about those leap days when reading this yesterday as well, but then I realised that calculating totals to YTD on leap days isn't going to be very accurate anyway.

Depending on how you "round" that leap day to the nearest date in a non-leap year, on leap days you're comparing totals for the last 60 days of a leap year to the last 59 days for a non-leap year - or you end up comparing the total of 366 days in a leap year to 365 days for non-leap years at the end of the year.

The point of YTD is of course to compare trends between years, is that concept broken wrt. leap years?

I suppose that the least incorrect solution is to round leap dates up to March 1st for non-leap years, so that the error is moved to the last day of the year - that's a far less significant error (1/366th) than the alternative (1/60th).

Still, you won't be able to calculate a valid date for the 366th day in a non-leap year, regardless of how you go about that...

...leap years? Really?

I always thought this forum was about finding solutions, not problems.

I'm sure everyone knows how to fix that.

1. Convert date to I8YYMD
2. if YY is dividable by 4* then substract 1
3. substract 10000
4. Convert I8YYMD to date



* that'll work till retiredment.
Or if you want to be sure: if MD = 0229 substract 1

@Wep5622

yes, that's an issue.
That's why we never use YTD based on days and month. Only on Week-numbers ( ISO-based ).

Always the exact same number of days and even compare number of non-working-days per week.

Only Easter and more lunar-based holidays are nasty.

Doesn't DATEADD take leap year into account? It won't return an non-existing day.

This will provide the same date for current year and last year.
The sample below has a starting date of Mar 1, 2012 and subtract 1 from it.

-*-SET &TODAY = &YYMD;
-SET &TODAY = '20120301';
-SET &YDAY = AYMD (&TODAY, -1, 'I8YYMD');
-SET &LYDAY = DATECVT(DATEADD(DATECVT(&YDAY,'I8YYMD','YYMD'),'Y','-1'),'YYMD','I8YYMD');

-TYPE &|YDAY == &YDAY
-TYPE &|LYDAY == &LYDAY

I notice that code rounds 20120229 minus 1 year down to 20110228, which in YTD means comparing day #60 in 2012 to day #59 in 2011.

To compare an equal amount of data in YTD (only up to the 365th day, of course) something like this would be needed:

-SET &TODAY = 20120229;
-SET &YDAY = DATECVT(&TODAY, 'I8YYMD', 'YYMD');
-SET &JAN1 = DATECVT(EDIT(&TODAY, '9999') || '0101', 'A8YYMD', 'YYMD');
-SET &DAY# = DATEDIF(&JAN1, &YDAY, 'D') +1;
-SET &LYJAN1 = DATEADD(&JAN1, 'Y', -1);
-SET &LYDAY# = IF &DAY# GT 365 THEN 365 ELSE &DAY#;
-SET &LYDAY = DATECVT(DATEADD(&LYJAN1, 'D', &LYDAY# -1), 'YYMD', 'I8YYMD');

-TYPE &|DAY# == &DAY#
-TYPE &|LYDAY# == &LYDAY#
-TYPE &|TODAY == &TODAY
-TYPE &|LYDAY == &LYDAY