Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2013Report 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, 2011Report 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, 2012Report 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, 2011Report 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, 2011Report 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: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report 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, 2005Report 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, 2003Report 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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report 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: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report 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, 2011Report 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, 2004Report 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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders