Focal Point
[SOLVED] Call "week ending" date variable in SUBHEAD [SOLVED]

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5637089476

March 05, 2015, 10:04 AM
Deej
[SOLVED] Call "week ending" date variable in SUBHEAD [SOLVED]
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
March 05, 2015, 10:49 AM
eric.woerle
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
March 05, 2015, 01:14 PM
Deej
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
March 05, 2015, 03:16 PM
Deej
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
March 05, 2015, 04:03 PM
Francis Mariani
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
March 05, 2015, 04:18 PM
Francis Mariani
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
March 30, 2015, 11:55 AM
Doug
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
March 30, 2015, 01:33 PM
Doug
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 ***

March 31, 2015, 05:07 AM
Alan B
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