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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
DATE = YESTERDAY
 Login/Join
 
Member
posted
I need to schedule a report to run daily, but I will need for it to report the prior day’s activity. I have a date field called VER_DATE on the table; I just need help with populating this date field with the prior date.

Example: When the report runs tomorrow morning at 8 am, it will be reporting activity that was done the day before.

Any help will be appreciated
 
Posts: 1 | Registered: November 02, 2006Report This Post
Expert
posted Hide Post
-SET &YESTERDAY = AYMD ( &YYMD , -1 , 'I8YYMD');
TABLE FILE ...
IF VER_DATE EQ &YESTERDAY
..
this will work if VER_DATE is either I8yymd or a focus smartdate.

get a copy of the USING FUNCTIONS manual, downloadable from the IBI documentation site.
It will have all this stuff in it.




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
Guru
posted Hide Post
Jennifer,

Since we never could get the Holiday file to work, here's how to do it if you want the previous business day.


-SET &W1=DOWK((&YYMD+0), '');


-*---------------------------------------------------------*-
-*                        YESTERCLM
-*
-* INCLUDE TO USED TO DETERMINE THE PREVIOUS WORKING DAY
-* TAKES INTO ACCOUNT HOLIDAYS
T1/MDYY=&MDYY;
T3/I2=IF '&W1' EQ 'MON' THEN 3 ELSE 1;
T4/MDYY=T1-T3;
T5/I8YYMD=T4;
T6/I8=T5;
-*
-*Looks at previous day to determine if that day was a holiday
-*If so, force it to look at the most recent work day
-*
TDATEI/I8 = DECODE T6(20060102 20051230
    20060414 20060413
    20060529 20060526
    20060704 20060703
    20060904 20060901
    20061123 20061122
    20061124 20061122
    20061225 20061222
    20061226 20061222
    20070707 20071229
    20070406 20070405
    20070528 20070525
    20070704 20070703
    20070903 20070831
    20071122 20071121
    20071123 20071121
    20071224 20071221
    20071225 20071221
    20080101 20071231
    20080321 20080320
    20080526 20080523
    20080704 20080703
    20080901 20080829
    20081127 20081126
    20081128 20081126
    20081225 20081224
    20081226 20081224);
TDATE/I8 = IF TDATEI EQ 0 THEN T6 ELSE TDATEI;


We simply update the include file (yestrclm) each year with our company holidays and closings.

Hope this helps.


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Virtuoso
posted Hide Post
Jennifer

Please update your signature with some info about your platform and application version.

There is a very good book that might help you a lot with all kind of date manipulations.
It gives you examples on things like smartdates, legacydates and all kind of formulas..




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
Master
posted Hide Post
Expanding on susannah's example, if you are using a database where the date has time-of-day in the field as well you will need the following:
TABLE FILE ...
WHERE VER_DATE GE '&YESTERDAY'
  AND VER_DATE LT '&YYMD'
...


Glenda, what is your platform? It took us a while to find the correct place for the holiday file but once we got it where the Reporting Server wanted it, it works great.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Guru
posted Hide Post
Our platform is AIX 5.2 operating system.


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Virtuoso
posted Hide Post
Glenda,

The holiday file in Windows is in the ibi\srv\wfs\bin directory. Each record has one holiday date, yyyymmdd characters. After each date one must have a space. Anything after that space is treated as a comment. The file name must be HDAYxxxx and the err extension. xxxx can be any 4 characters.

In order to find yesterday's date, you have to declare the name of the holiday file:
SET HDAY=xxxx
and the business days, usually:
SET BUSDAYS=_MTWTF_

Then you define yesterdays date in DEFINE:
  
DEFINE FILE ...
TODAY/YYMD='&DATEYYMD';
YESTERDAY/YYMD=DATEADD(TODAY, 'BD', -1);
END

And in TABLE:
TABLE FILE ...
...
WHERE VERDATE EQ YESTERDAY
...
END

Good luck!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders