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] Call "week ending" date variable in SUBHEAD [SOLVED]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Call "week ending" date variable in SUBHEAD [SOLVED]
 Login/Join
 
Gold member
posted
I need to set a variable called &WEEKEND that will pull the date from the upcoming Saturday and then pull that variable into a SUBHEAD in my report.

ON TABLE SUBHEAD
"Weekly Leader Board"
"Week ending &WEEKEND"

The report I'm working in was created by another developer that is no longer with the company and there are several other variables already set in the report. I'm just having trouble figuring out how to set this new variable that I need.

Here is a list of other variables defined at the top of this report, including the new one I need to set:

-SET &HOUR = EDIT(&TOD,'99');
-SET &M = IF &TOD GT '12.00.00' THEN 'pm' ELSE 'am';
-SET &FLAG = IF &TOD GT '13.00.00' THEN 1 ELSE 0 ;
-SET &MINUTES = EDIT(&TOD,'$$$99');
-SET &SECONDS = EDIT(&TOD,'$$$$$$99');
-SET &JTIME = &HOUR | ':' | &MINUTES | &M;
-SET &MORROW = IF &HOUR GE 6 THEN DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')), 'D', +2)), 'YYMD','I8YYMD') ELSE DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')), 'D', 0)), 'YYMD','I8YYMD');
-SET &WEEKEND = ???

Thanks for any suggestions!

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



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Master
posted Hide Post
I would use the datemov function. Here is a snippet from the helpfile on it.

Available Languages: reporting, Maintain

The DATEMOV function moves a date to a significant point on the calendar. 

Since Dialogue Manager interprets a date as alphanumeric or numeric, and DATEMOV requires a standard date stored as an offset from the base date, do not use DATEMOV with Dialogue Manager unless you first convert the variable used as the input date to an offset from the base date. For example, the following converts the integer legacy date 20050131 to a smart date, adds one month, and converts the result to an alphanumeric legacy date:

-SET &STRT=DATECVT(20050131,'I8YYMD', 'YYMD');         
-SET &NMT=DATEADD(&STRT,'M',1);                        
-SET &NMTA=DATECVT(&NMT,'YYMD','A8MTDYY');             
-TYPE A MONTH FROM 20050131 IS &NMTA 
The output shows that the DATEADD function added the actual number of days in the month of February to get to the end of the month from the end of January:

A MONTH FROM 20050131 IS 02282005
For more information, see Calling a Function From a Dialogue Manager Command.

DATEMOV works only with full component dates. 


--------------------------------------------------------------------------------
 Top of page 
--------------------------------------------------------------------------------
 

xx 
Syntax: How to Move a Date to a Significant Point
DATEMOV(date, 'move-point')
where:

date
Date

Is the date to be moved. It must be a full component format date (for example, MDYY or YYJUL). 

move-point
Alphanumeric

Is the significant point the date is moved to enclosed in single quotation marks. An invalid point results in a return code of zero. Valid values are:

EOM, which is the end of month.
BOM, wihch is the beginning of month.
EOQ, which is the end of quarter.
BOQ, which is the beginning of quarter.
EOY, which is the end of year.
BOY, which is the beginning of year.
EOW, which is the end of week.
BOW, which is the beginning of week.
NWD, which is the next weekday.
NBD, which is the next business day.
PWD, which is the prior weekday.
PBD, which is the prior business day.
WD-, which is a weekday or earlier.
BD-, which is a business day or earlier.
WD+, which is a weekday or later.
BD+, which is a business day or later.
A business day calculation is affected by the BUSDAYS and HDAY parameter settings.

Also note that when the DATEMOV function calculates the next or previous business day or work day, it always starts from a business day or work day. So if the actual day is Saturday or Sunday, and the request wants to calculate the next business day, DATEMOV will use Monday as the starting day, not Saturday or Sunday, and will return Tuesday as the next business day. Similarly, when calculating the previous business day, it will use the starting day Friday, and will return Thursday as the previous business day. 

To avoid skipping a business day or work day, use DATEMOV twice. To return the next business or work day, use BD- or WD- to first move to the previous business or work day (if it is already a business day or work day, it will not be moved). Then use DATEMOV again to move to the next business or work day. If you want to return the previous business or work day, first use BD+ or WD+ to move to the next business or work day (if it is already the correct type of day, it will not be moved). Then use DATEMOV again to return the previous business or work day.

Note: DATEMOV does not use an output argument. It uses the format of the date argument for the result. As long as the result is a full component date, it can be assigned only to a full component date field or to an integer field.
  


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Gold member
posted Hide Post
Thanks for the reply Eric.

I understand the top of your snippet about converting the date but I don't understand how that conversion plays into the DATEMOV function (as described below the "Top of page").

When I try this:

-SET &STRT=DATECVT(20150305,'I8YYMD', 'YYMD');
-SET &NMT=DATEADD(&STRT,'M',1);
-SET &NMTA=DATECVT(&NMT,'YYMD','A8MTDYY');

-SET &WEEKEND = DATEMOV(&NMTA, 'BOM');
-SET &WEEKEND2 = DATEMOV(&NMTA, 'EOM');
-SET &WEEKEND3 = DATEMOV(&NMTA, 'BOY');
-SET &WEEKEND4 = DATEMOV(&NMTA, 'EOY');

-TYPE A WEEK FROM 20150305 IS &NMTA
-TYPE WEEKEND IS &WEEKEND (BOM)
-TYPE WEEKEND2 IS &WEEKEND2 (EOM)
-TYPE WEEKEND3 IS &WEEKEND3 (BOY)
-TYPE WEEKEND4 IS &WEEKEND4 (EOY)


The result is:

A WEEK FROM 20150305 IS 04052015
WEEKEND IS 129950101 (BOM)
WEEKEND2 IS 129950131 (EOM)
WEEKEND3 IS 129950101 (BOY)
WEEKEND4 IS 129951231 (EOY)


I've tried a million different variables for the date in the DATEMOV function and can't get it to spit out anything resembling the BOM, EOM, BOY, EOY as I expect.

I just need WEEKEND to be the next Saturday... no matter what the date is.



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Gold member
posted Hide Post
I found the answer here. Thanks Francis!

Smiler


quote:
Originally posted by Francis Mariani:
So does this:

-SET &MTDF = DATECVT( DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOM'),'YYMD','I8YYMD');

The innermost DATECVT function call converts the integer value &YYMD to a smart date. Then the DATEMOV function runs and then the outermost DATECVT function converts the smart date back to an integer.



WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 67 | Registered: May 21, 2014Report This Post
Expert
posted Hide Post
This is a post from many years ago: Simplifying the use of Date functions in Dialogue Manager. In it I described how to create a DEFINE FUNCTION that can be called by Dialogue Manager. The example I created did this for one Date function only, but could probably be modified to apply a user selected Date function.

You create a library of functions as a fex and -INCLUDE where required. An example:

DEFINE FUNCTION UDATEADD(INDATE/A8YYMD, INUNIT/A2, INNBR/I4)
INDATE1/YYMD    = INDATE;
UDATEADD1/YYMD  = DATEADD(INDATE1, INUNIT, INNBR);
UDATEADD/A8YYMD = UDATEADD1;
END
-RUN

You call it with this simple Dialogue manager code:
-SET &DATE1 = UDATEADD(&YYMD, 'M', -3);


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
Expert
posted Hide Post
Oh, and most likely this is not in the 1001 Things To Do With Dates book...


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
Expert
posted Hide Post
Thanks Francis, That brings it even closer to the already "(Almost) 1001 Things"... I'll use this soon.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
While I like that DEFINE FUNCTION, why doesn't the following work in WebFOCUS 8, it returns "*** &DATEADD6=20150334 ***"?
-SET &DATEIN = 20150328 ;
-*               DATEADD(date, 'component', increment)
-SET &DATEADD6 = DATEADD(&DATEIN, 'D' , 6 ) ;
-TYPE *** &|DATEADD6=&DATEADD6 ***
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
quote:
While I like that DEFINE FUNCTION, why doesn't the following work in WebFOCUS 8, it returns "*** &DATEADD6=20150334 ***"?
-SET &DATEIN = 20150328 ;
-* DATEADD(date, 'component', increment)
-SET &DATEADD6 = DATEADD(&DATEIN, 'D' , 6 ) ;
-TYPE *** &|DATEADD6=&DATEADD6 ***


There are 3 types of dates, legacy dates, dates and date-time.

Legacy dates are held in readable format, dates and date-time are held as numeric difference from a base date (internal format).

In Dialogue Manager when you set a date it is a legacy date format, the same as using I8YYMD or A8YYMD (and other valid formats) in DEFINE etc.

There are a set of functions that work with legacy dates, different functions for date and others for date-time. To use date functions, the date must be in internal format, hence the use of DATECVT.

To add days or months with legacy dates, then use the correct function:
-SET &DATEIN = 20150328 ;
-SET &DATEADD5 = AYMD(&DATEIN,6,'I8');
-TYPE *** &|DATEADD5=&DATEADD5 ***


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | 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     [SOLVED] Call "week ending" date variable in SUBHEAD [SOLVED]

Copyright © 1996-2020 Information Builders