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: Due not resuscitate] Date madness again

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED: Due not resuscitate] Date madness again
 Login/Join
 
Master
posted
Of course, this should be simple...

I have this WHERE clause in a fex:

WHERE ORDER_DATE GE &ORDER_START_DATE.(|FORMAT=MDYY).Order start date.QUOTEDSTRING AND ORDER_DATE LE &ORDER_END_DATE.(|FORMAT=MDYY).Order end date.QUOTEDSTRING;

So when I run the fex I enter start date "12/11/2016" and end date "12/17/2016". All is good in the world so far. The dates work in the WHERE clause and even appear in the fex title output.

Now I want to extract the name of the day from both date inputs, i.e., "Sunday" and "Saturday."

So what god do I have to pray to in order to make that happen?

I have the "WebFOCUS Keysheet Quick (ahem) Reference Guide" and "1001 Ways to Work with DATES in WebFOCUS." They look nice on my bookshelf, but in this situation, that's all they do for me.

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


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
DOWK function
you need the Functions Manual...that's the bible. download it, it's the best.

DOWK and DOWKL: Finding the Day of the Week
How to: Find the Day of the Week
Available Languages: reporting, Maintain
The DOWK and DOWKL functions find the day of the week that corresponds to a date. DOWK
returns the day as a three letter abbreviation; DOWKL displays the full name of the day.
Syntax: How to Find the Day of the Week
{DOWK|DOWKL}(indate, output)
where: indate I6YMD or I8YMD
Is the legacy date in year-month-day format. If the date is not valid, the function returns
spaces. If the date specifies a two digit year and DEFCENT and YRTHRESH values have
not been set, the function assumes the 20th century.
output
DOWK: A4. DOWKL: A12
Is the name of the field that contains the result, or the format of the output value
enclosed in single quotation marks.

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




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
Master
posted Hide Post
Thanks, susannah, but that does not work for me. I get error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error after error no matter what I do.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
Try this

 DEFINE FILE CAR
TDAY/YYMD=&YYMD;
TDAY_WK/Wtr=TDAY;
END
TABLE FILE CAR 
PRINT TDAY_WK
BY CAR
END 


Day of the week is just a format.


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
Master
posted Hide Post
Taking Eric's method and adding comments:
-* File Dates.fex
-DEFAULTH &ORDER_START_DATE = '12/11/2016'
-DEFAULTH &ORDER_END_DATE   = '01/16/2017'
-*
DEFINE FILE CAR
-* Convert alphanumeric input to smart date format.
 ORDER_START_DATE_SMART/MDYY WITH CAR = '&ORDER_START_DATE';
 ORDER_END_DATE_SMART/MDYY WITH CAR = '&ORDER_END_DATE';
-* Convert format to Day of Week.
 ORDER_START_DATE_WTR/Wtr=ORDER_START_DATE_SMART;
 ORDER_END_DATE_WTR/Wtr=ORDER_END_DATE_SMART;
END
TABLE FILE CAR
HEADING
"Order Dates &ORDER_START_DATE (<ORDER_START_DATE_WTR<-1 ) thru &ORDER_END_DATE (<ORDER_END_DATE_WTR<-1 )"
PRINT CAR NOPRINT
IF RECORDLIMIT EQ 1
END  

Yields:
PAGE 1 

Order Dates 12/11/2016 (Sunday) thru 01/16/2017 (Monday) 

This message has been edited. Last edited by: David Briars,
 
Posts: 822 | Registered: April 23, 2003Report This Post
Expert
posted Hide Post
Squatch,

What is the format of the ORDER_DATE field ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
ORDER_DATE is MDYY ("Smart"date). That is used in a WHERE clause. But the dates to show seem to be the prompt variables ORDER_START_DATE and ORDER_END_DATE.

The real issue turns out to be the HEADING clause:

HEADING
"Lab Orders by Day of Week/Hour "
"&ORDER_START_DATE  to  &ORDER_END_DATE "

The second heading line will not show up if I specify the heading to be embedded:

ON GRAPH SET EMBEDHEADING ON

If I switch off embedded heading the second heading line with the date range appears. But then I have to scroll down to see my FOOTER. If embedding is specified the graph appears in whole without a scroll bar in the browser, which is what I want. But without the second HEADING line with the crucial date range displayed.

I'm going to close this because I have wasted WAY too much time trying to get such a simple thing to work in WebFOCUS. It's not worth the time spent on it. Perhaps other software is the way to go in the future.

Thanks for everyone who attempted to help, however, it is appreciated.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
The DOWK function works on an I8, not a smart date. which it says in the Functions Manual. and in my example above.
If you want the DayofWeek to show in the heading, then use the DOWK function on some version of the two &vars. Make sure the &vars are (or you can get them) in the format I8YYMD




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
Master
posted Hide Post
quote:
Originally posted by susannah:
If you want the DayofWeek to show in the heading, then use the DOWK function on some version of the two &vars.

Thanks, susannah, but the problem turned out to be an EMBEDHEADING problem. I can get the start date and end date to show up if they are part of a single title heading. If placed on a second title heading line, they will not show up if EMBEDHEADING is turned on.

If EMBEDHEADING is turned off, the dates are there on the second title heading line, but the user must scroll down in the browser to see the chart's footer (Which contains important information). It's not worth the time to try to troubleshoot something like that. I am tired of dealing with weird WebFOCUS quirkiness and will pursue another option.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
If I understand this thread correctly, the requirements are:
* Incoming start and end dates - alphanumeric format - MM/DD/YYYY.
* Calculate day of week for each date.
* Embed dates and days of week in a visualization heading.
-*
-* Incoming dates.  
-*
-DEFAULTH &ORDER_START_DATE = '12/11/2016'
-DEFAULTH &ORDER_END_DATE   = '01/16/2017'
-*
-* Calculate Day of Week for each incoming date.  
-*
DEFINE FILE CAR
-* Convert alphanumeric input to smart date format.
 ORDER_START_DATE_SMART/MDYY WITH CAR = '&ORDER_START_DATE';
 ORDER_END_DATE_SMART/MDYY WITH CAR = '&ORDER_END_DATE';
-* Convert format to Day of Week.
 ORDER_START_DATE_WTR/Wtr = ORDER_START_DATE_SMART;
 ORDER_END_DATE_WTR/Wtr = ORDER_END_DATE_SMART;
END
TABLE FILE CAR
 PRINT CAR NOPRINT
 COMPUTE SDOWK/A12 = FPRINT(ORDER_START_DATE_WTR, 'Wtr', 'A12');
 COMPUTE EDOWK/A12 = FPRINT(ORDER_END_DATE_WTR, 'Wtr', 'A12');
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE AS SVDOWK
IF RECORDLIMIT EQ 1
END
-RUN
-READ SVDOWK &OSD_DOWK.A12. &OED_DOWK.A12. 
-TYPE &OSD_DOWK &OED_DOWK   
-RUN
-*
-* Create visualization for user.  
-*
GRAPH FILE GGSALES
SUM DOLLARS BUDDOLLARS UNITS BUDUNITS
BY PRODUCT
WHERE CATEGORY EQ 'Gifts'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET AUTOFIT ON
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
 border:{color:'blue'},
 chartFrame: {border: {width:2, color:'red'}},
 legend: {position:'right'},
 title: {
         text: 'Gifts - Actual v. Budget',
         visible: true,
         font: 'Bold 20pt Courier',
         align: 'center',
         color: 'blue'},
 subtitle: {
            text: 'Order Dates &ORDER_START_DATE (&OSD_DOWK) thru &ORDER_END_DATE (&OED_DOWK)',
            font: 'Bold 16pt Comic Sans MS',
            visible: true,
            align: 'center',
            color: 'red'}
*END
ENDSTYLE
END  

For visualizations/JSCHARTS, I tend to use the API properties as much as possible, instead to the more 'legacy' commands.
 
Posts: 822 | Registered: April 23, 2003Report This Post
Master
posted Hide Post
The date issue turned out to be a red herring. The real problem is embedded headings.

And I don't know about the legacy issues, but I do know that InfoAssist allows a developer to specify embedded headings. That is implemented through the "ON GRAPH SET EMBEDHEADING ON" line in the code.

Here is a modified version of the code provided by the previous poster illustrating the issue. Try toggling the embedded heading command from ON to OFF to see the difference:

-*
-* Incoming dates.  
-*
-DEFAULTH &ORDER_START_DATE = '12/11/2016'
-DEFAULTH &ORDER_END_DATE   = '01/16/2017'
-*
-* Calculate Day of Week for each incoming date.  
-*
DEFINE FILE CAR
-* Convert alphanumeric input to smart date format.
 ORDER_START_DATE_SMART/MDYY WITH CAR = '&ORDER_START_DATE';
 ORDER_END_DATE_SMART/MDYY WITH CAR = '&ORDER_END_DATE';
-* Convert format to Day of Week.
 ORDER_START_DATE_WTR/Wtr = ORDER_START_DATE_SMART;
 ORDER_END_DATE_WTR/Wtr = ORDER_END_DATE_SMART;
END
TABLE FILE CAR
 PRINT CAR NOPRINT
 COMPUTE SDOWK/A12 = FPRINT(ORDER_START_DATE_WTR, 'Wtr', 'A12');
 COMPUTE EDOWK/A12 = FPRINT(ORDER_END_DATE_WTR, 'Wtr', 'A12');
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE AS SVDOWK
IF RECORDLIMIT EQ 1
END
-RUN
-READ SVDOWK &OSD_DOWK.A12. &OED_DOWK.A12. 
-TYPE &OSD_DOWK &OED_DOWK   
-RUN
-*
-* Create visualization for user.  
-*
GRAPH FILE GGSALES
SUM DOLLARS BUDDOLLARS UNITS BUDUNITS
BY PRODUCT
WHERE CATEGORY EQ 'Gifts'
HEADING
"Gifts - Actual v. Budget "
"&ORDER_START_DATE  to  &ORDER_END_DATE "
FOOTING
"My footer with important information that won't be seen unless the user scrolls down; caused by 'ON GRAPH SET EMBEDHEADING OFF'. "
"Turning EMBEDHEADING ON will show the footer without needing to scroll down, but the date range will disappear from the title header. "
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET EMBEDHEADING ON
-*ON GRAPH SET EMBEDHEADING OFF
ON GRAPH SET AUTOFIT ON
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
 border:{color:'blue'},
 chartFrame: {border: {width:2, color:'red'}},
 legend: {position:'right'},
-* title: {
-*         text: 'Gifts - Actual v. Budget',
-*         visible: true,
-*         font: 'Bold 20pt Courier',
-*         align: 'center',
-*         color: 'blue'},
-* subtitle: {
-*            text: 'Order Dates &ORDER_START_DATE (&OSD_DOWK) thru &ORDER_END_DATE (&OED_DOWK)',
-*            font: 'Bold 16pt Comic Sans MS',
-*            visible: true,
-*            align: 'center',
-*            color: 'red'}
*END
ENDSTYLE
END  


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Master
posted Hide Post
Adding 'Visualization needs a footnote' to the specs...
-*
-* Incoming dates.  
-*
-DEFAULTH &ORDER_START_DATE = '12/11/2016'
-DEFAULTH &ORDER_END_DATE   = '01/16/2017'
-*
-* Calculate Day of Week for each incoming date.  
-*
DEFINE FILE CAR
-* Convert alphanumeric input to smart date format.
 ORDER_START_DATE_SMART/MDYY WITH CAR = '&ORDER_START_DATE';
 ORDER_END_DATE_SMART/MDYY WITH CAR = '&ORDER_END_DATE';
-* Convert format to Day of Week.
 ORDER_START_DATE_WTR/Wtr = ORDER_START_DATE_SMART;
 ORDER_END_DATE_WTR/Wtr = ORDER_END_DATE_SMART;
END
TABLE FILE CAR
 PRINT CAR NOPRINT
 COMPUTE SDOWK/A12 = FPRINT(ORDER_START_DATE_WTR, 'Wtr', 'A12');
 COMPUTE EDOWK/A12 = FPRINT(ORDER_END_DATE_WTR, 'Wtr', 'A12');
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE AS SVDOWK
IF RECORDLIMIT EQ 1
END
-RUN
-READ SVDOWK &OSD_DOWK.A12. &OED_DOWK.A12. 
-TYPE &OSD_DOWK &OED_DOWK   
-RUN
-*
-* Create visualization for user.  
-*
GRAPH FILE GGSALES
SUM DOLLARS BUDDOLLARS UNITS BUDUNITS
BY PRODUCT
WHERE CATEGORY EQ 'Gifts'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET AUTOFIT ON
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET STYLE *
*GRAPH_JS
 border:{color:'blue'},
 chartFrame: {border: {width:2, color:'red'}},
 legend: {position:'right'},
 title: {
         text: 'Gifts - Actual v. Budget',
         visible: true,
         font: 'Bold 20pt Courier',
         align: 'center',
         color: 'blue'},
 subtitle: {
            text: 'Order Dates &ORDER_START_DATE (&OSD_DOWK) thru &ORDER_END_DATE (&OED_DOWK)',
            font: 'Bold 16pt Comic Sans MS',
            visible: true,
            align: 'center',
            color: 'red'},
 footnote: {
            text: 'My footer with very important information. \n Hopefully this footer will be read by all.',
            font: 'Bold 10pt Arial',
            visible: true,
            align: 'left',
            color: 'purple'}
*END
ENDSTYLE
END    
 
Posts: 822 | Registered: April 23, 2003Report This Post
Master
posted Hide Post
Well, that looks very nice but is this confirmation that ON GRAPH SET EMBEDHEADING ON/OFF is broken?

The reason I ask is so I can explain to our non-App Studio users who only know InfoAssist that they will need to occasionally pass on some of their work to me to correct issues like this.


App Studio
WebFOCUS 8.1.05M
Windows, All Outputs
 
Posts: 594 | Location: Michigan | Registered: September 04, 2015Report This Post
Expert
posted Hide Post
I am intrigued with EMBEDHEADING, and would also like to if its broken, because it could solve some issues here.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
According to IBI, you can do EVERYTHING in InfoAssist... *dripping sarcasm*


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
Expert
posted Hide Post
Yikes! Everything's gone GUI.


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
When all else fails, remember "edastart -t"

Cool


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
But by the time I find it in the GUI I could have written a 100 lines of code.


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
  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: Due not resuscitate] Date madness again

Copyright © 1996-2020 Information Builders