Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] YTD same day/month in previous year as current year.
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] YTD same day/month in previous year as current year.
 Login/Join
 
Member
posted
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
 
Posts: 17 | Registered: May 03, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 7.7.03
 
Posts: 67 | Registered: January 05, 2011Reply With QuoteReport This Post
Silver Member
posted Hide Post
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;


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 103 | Registered: April 27, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 103 | Registered: April 27, 2011Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
leap years
faites attention




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1663 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Master
posted Hide Post
...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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Master
posted Hide Post
@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. Wink


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 103 | Registered: April 27, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


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 :
 
Posts: 1663 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] YTD same day/month in previous year as current year.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.