[CLOSED] YTD same day/month in previous year as current year.
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>,
WebFocus 8
January 31, 2014, 06:49 AM
linnex
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.
8009 Windows, HTML, AHTML, Excel In FOCUS since 1983
January 31, 2014, 10:24 AM
Kevin W
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;
WebFOCUS 7.7.05 (Someday 8) Windows 7, All Outputs In Focus since 1983.
January 31, 2014, 10:28 AM
Kevin W
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;
WebFOCUS 7.7.05 (Someday 8) Windows 7, All Outputs In Focus since 1983.
February 03, 2014, 04:02 AM
Dave
Convert to I8yyyymmdd and substract 10000, then convert back.
i.m.o. simplest way,
greets
_____________________ WF: 8.0.0.9 > going 8.2.0.5
February 03, 2014, 09:45 AM
j.gross
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.
February 03, 2014, 03:16 PM
susannah
leap years faites attention
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 04, 2014, 03:55 AM
Wep5622
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...
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 04, 2014, 04:32 AM
Dave
...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
_____________________ WF: 8.0.0.9 > going 8.2.0.5
February 04, 2014, 04:37 AM
Dave
@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.
_____________________ WF: 8.0.0.9 > going 8.2.0.5
February 04, 2014, 11:46 AM
Kevin W
Doesn't DATEADD take leap year into account? It won't return an non-existing day.
WebFOCUS 7.7.05 (Someday 8) Windows 7, All Outputs In Focus since 1983.
February 04, 2014, 04:26 PM
Spence
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.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :