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     [SOLVED] Calculating # of months between year-month variable and date-time field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Calculating # of months between year-month variable and date-time field
 Login/Join
 
JohnB
Platinum Member
posted
I thought this would be fairly simple, but I have tried many approaches and cannot get this to work correctly.

The user inputs the month number and 4 digit year into 2 variables. This is the end date. The start date is a date-time field with a format of HYYMDs.

I need to create a field that calculates the difference in months between the two dates.

How is this done?

Thank you.

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


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
There are several ways to get this done
The best way is by the use of so called smart dates

Once you have two smartdates you have some formulas to calculate the difference in days, weeks, months, years , business days .
Step one convert a yymds format to a YYMD format
SDATE/YYMD=HDATE(startdate,'ymd');
Step two concatenation the input year month to a string holding something like 20120701
tdate/A8YYMD='&yy'||'&mm'||'01';
Step 3 convert the string to a smartdate.
EDATE/YYMD=tdate;
DIFF/I2=DATEDIFF(EDATE,SDATE,'m'); for month difference

Btw this should all be in the manual or the on line help files

Nb there might be a typo in the formulas since I do not have the manuals ad hand.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
JohnB
Platinum Member
posted Hide Post
I thought it should be simple like your example.

Thank you.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
JohnB
Platinum Member
posted Hide Post
Another question on DATEDIF.

When working with 2 smart dates, and the start date could be within the last few years and the end date is in greater than or equal to the start date DATEDIF works fine. DATEDIF also works fine if end date is earlier the start date (this was a test). In this case the number of months between the 2 dates is a negative number

Many times the end date is 01/01/1900 and DATEDIF gives a number months diference in the thousands, which is incorrect. There's not over 10,000 months beween 01/01/1900 and 01/01/2013.

Why does DATEDIF do this?

Thank you.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
If your input variables for month and year are empty, it probably will give that date

If you want to test it, you might start with typing the result and see what you get
A next step could be to evaluate the entered values and if the year is less then a certain value you loop back and ask for a correct value.
You also might add a default value of let's say today in those fields.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
JohnB
Platinum Member
posted Hide Post
In this case, Frank, the dates are from the data file. The end date in the data file may times has a value of 01/01/1900.

So if a date field is empty it will display a value of 01/01/1900?

If that's the case, then it should be easy to get DATEDIF to work, by adding a little extra coding.

Thank you.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report 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     [SOLVED] Calculating # of months between year-month variable and date-time field

Copyright © 1996-2020 Information Builders