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] Datedif Business Day Calculation Not Working

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Datedif Business Day Calculation Not Working
 Login/Join
 
Gold member
posted
We've ran into a very strange issue recently where the business day calculation part of DATEDIF is not working. I'm running the following code and it is producing a value of 0. Has anyone ran into this before and know what might be causing it? Calendar day calculations seem to work fine. I've opened a ticket with IBI, but haven't got very far yet.

-SET &ECHO=ALL;
-SET &DATETEST= DATEDIF(20150901,20150908,'BD');
-TYPE &DATETEST;


 -SET &DATETEST= DATEDIF(20150901,20150908,'BD');
 -TYPE 0;
 0;


With Calendar Days instead:
-SET &DATETEST= DATEDIF(20150901,20150908,'D');
 -TYPE 7;
 7;


Also, I forgot to mention, this seems to be sporadic in that the calculation will work sometimes, but I cannot consistently reproduce it. It is enough to be an issue though that is causing reports not to calculate correctly. We have also added a HDAY file recently, but I am not referencing it in any of the procedures that are having issues, could there still be conflicts?

edit: So I ran it again, about 15 minutes later and it is now calculating the difference correctly. The issue is not corrected though as it has been doing this inconsistent calculation for a while now.
-SET &DATETEST= DATEDIF(20150901,20150908,'BD');
 -TYPE 5;
 5;


Thanks,
Clint

This message has been edited. Last edited by: capples,


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Report This Post
Virtuoso
posted Hide Post
DATEDIF takes two "smart" dates as arguments; you need to apply DATECVT to convert your two yyyymmdd integer values into offset values.

Search for DATECVT to find examples.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
I tried converting to YYMD and I'm still seeing the same results.

-SET &DATETEST= DATEDIF(DATECVT(20150901,'I8YYMD','YYMD'),DATECVT(20150908,'I8YYMD','YYMD'),'BD');
 -TYPE 0;
 0;



Also, in the Datedif examples in the documentation, they also use I8YYMD. (See Here). I never noticed this issue previously, so I don't know if it's something new that has popped up due to a change somewhere or if it's always been there and is just inconsistent on when it occurs.


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Report This Post
Virtuoso
posted Hide Post
quote:
in the Datedif examples in the documentation, they also use I8YYMD


Not really.

The examples there are in the context of FOCUS (DEFINE) rather than Dialog Manager (-SET). When an alpha value is provided (such as "'19990228'" or "'March 31 2001'") it is cast as a /YYMD date before the function performs its operations. That probably happens in DEFINE, before the functions sees the arguments. (Cf. the computes in the Maintain example) Since D.M. is unaware of "dates" (all values are alpha or simple numeric), that cast may not occur, and 20150809 may just be passed as a large positive integer value (understood by DATEDIF as a date offset), and it's anyone's guess how it will react when asked to determine the day of week and determine whether it is a holiday.

I'm curious what happens when you substitute 'WD' for 'BD'.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
I see what you're saying now. However, when I pass anything that doesn't involve Business Days, the calculation seems to work correctly. As you suggested, here's the WD calc.

 -SET &DATETEST= DATEDIF(20150901,20150908,'WD');
 -TYPE 5;
 5;


Here's with PBD for another example
-SET &DATETEST= DATEDIF(20150901,20150908,'PBD');
 -TYPE 0;
 0;


I just don't have any ideas what could cause this. Thanks for your thoughts so far because I'm stumped and still fairly new to FOCUS overall, so I don't understand all of the nuances.


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Report This Post
Expert
posted Hide Post
As Jack previously mentioned, you MUST use DATECVT when using a Date function in a Dialogue Manager -SET statement - this has been the case from the very beginning.

It's pure coincidence that your example worked - probably because the two dates were in the same month.

Try this test, it doesn't work:
-SET &DATETEST2= DATEDIF(20151224,20160108,'WD');

-TYPE &DATETEST2

But this does:
-SET &DATETEST3= DATEDIF(DATECVT(20151224, 'I8YYMD', 'YYMD'),DATECVT(20160108, 'I8YYMD', 'YYMD'),'WD');

-TYPE &DATETEST3


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
You're correct regarding the date format issue, I checked my original files that were having issues with date calcs and they are in the YYMD format.

Even when I take your code and change it to BD, I still receive the calculation error (see DATETEST4 below).

 -SET &DATETEST= DATEDIF(20150901,20150908,'BD');
 -TYPE 0;
 0;
 -RUN
 -SET &DATETEST2= DATEDIF(20151224,20160108,'WD');
 -TYPE 6346
 6346
 -RUN
 -SET &DATETEST3= DATEDIF(DATECVT(20141224, 'I8YYMD', 'YYMD'),DATECVT(20150108, 'I8YYMD', 'YYMD'),'WD');
 -TYPE 11
 11
 -RUN
 -SET &DATETEST4= DATEDIF(DATECVT(20141224, 'I8YYMD', 'YYMD'),DATECVT(20150108, 'I8YYMD', 'YYMD'),'BD');
 -TYPE 0
 0
 -RUN


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Report This Post
Virtuoso
posted Hide Post
I'm on a different release, but when I run this code I get the correct output:

-SET &DATETEST3= DATEDIF(DATECVT(20141224, 'I8YYMD', 'YYMD'),DATECVT(20150108, 'I8YYMD', 'YYMD'),'WD');
-TYPE &DATETEST3
11

-SET &DATETEST4= DATEDIF(DATECVT(20141224, 'I8YYMD', 'YYMD'),DATECVT(20150108, 'I8YYMD', 'YYMD'),'BD');
-TYPE &DATETEST4
9


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
If you have a HDAY file and want to use DB in the date functions then you need to have at least one entry in your HDAY file for each year that is used in the function otherwise it will return 0.

I believe this is documented somewhere, but don't have a reference. here it is


quote:
Reference: Rules for Creating a Holiday File
  • Dates must be in YYMD format.
  • Dates must be in ascending order.
  • Each date must be on its own line.
  • Each year for which data exists must be included or the holiday file is considered invalid. Calling a date function with a date value outside the range of the holiday file returns a zero for business day requests.

This message has been edited. Last edited by: StuBouyer,


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Member
posted Hide Post
Good call StuBouyer.
I was able to recreate Capples behavior by createing an empty HDAY file. And it will only effect BD option.


All Releases
All OS, All Outputs
 
Posts: 15 | Registered: October 24, 2014Report This Post
Gold member
posted Hide Post
I'll take a look at our HDAY file then, I suspect that you are right as this behavior only started happening recently with this addition (also right around when we upgraded to 8104, bad timing).

Thanks for the responses, I'll check back after we test this.

Clint


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Report This Post
Gold member
posted Hide Post
So it appears the HDAY file is the culprit. If I do not add:

FILEDEF HDAYDOFF DISK E:\IBI\APPS\DATES\HDAYDOFF.ERR
SET BUSDAYS = _MTWTF_
SET HDAY = DOFF


when using the BD date type, it will return 0s. When this code is added, it will return the right number of BDs. It seems like we will have to add this line each time or use WD in the calcs if the holidays don't matter.

Thanks for the help.


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Report This Post
Guru
posted Hide Post
You should be able to add those lines to your server profile - edasprof.prf

That way it will always be set for all reports


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report 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] Datedif Business Day Calculation Not Working

Copyright © 1996-2020 Information Builders