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]Sorting Graph with NOPRINT field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Sorting Graph with NOPRINT field
 Login/Join
 
Member
posted
I have a graph with monthyear on the X axis which sorts alphabetically. In my table I have another field "monthyearsort" that I want to use for the sort but to display the monthyear.
I have searched through Focal Point but can not find a solution that fits this situation.
Thanks for any help.
Here is the code:

GRAPH FILE csi_operations/techopsmetrics

SUM COMPUTE TOTAL_HOURS_SAVED/D12.2=TECHOPSMETRICS.TOTAL_MINS_SAVED/60 ;
BY MANAGERORG
ACROSS MONTHYEARSORT NOPRINT
ACROSS MONTHYEAR
WHERE DIRECTOR EQ '&DIRECTOR.(FIND TECHOPSMETRICS.TECHOPSMETRICS.DIRECTOR IN TECHOPSMETRICS |FORMAT=A255V).DIRECTOR:.';
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET ARGRAPHENGIN JSCHART
ON GRAPH SET GRWIDTH 1
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 3
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *


This actually does sort the data correctly but displays the monthyearsort field instead of the monthyear field. Example of MonthYear field = January2016. Example of MonthYearSort field = 201601.

App Studio 8105

This message has been edited. Last edited by: <Emily McAllister>,


7702, Windows7, html
 
Posts: 10 | Location: Minneapolis, Mn | Registered: February 27, 2013Report This Post
Platinum Member
posted Hide Post
Did you try changing the name of the monthyear field to something else? Perhaps WebFOCUS is assuming that this is a truncation of monthyearsort.


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Report This Post
Expert
posted Hide Post
Hi Frank,

Welcome to the point!

Per Vivian, this is going to be due to an error with your derivation of the MONTHYEAR. Not sure how your are deriving this (DEFINE or COMPUTE and how?) but the best option is to use an internal date format if you can, then you wouldn't require the additional NOPRINT column.

However, if you do require the full month name in the output and the recipient doesn't like the fact that a format of "MtYY" gives results such as "Jan, 1996", then it may be a moot point Frowner

Take the following example code. There are two methods of deriving MONTHYEAR. The first one requires the additional NOPRINT column whereas the second does not.

DEFINE FILE GGSALES
  YEAR/YY = DATE;
  MONTHYEARSORT/YYM = DATE;
  MONTHYYMD/A8YYMD = DATECVT(DATE,'I8YYMD','A8YYMD');
  MONTHYEAR/A17 = LCWORD(17, CHGDAT('YYMD', 'MYYX', MONTHYYMD, 'A17'), 'A17');
-*  MONTHYEAR/MtYY = MONTHYYMD;
END
-*
GRAPH FILE GGSALES
  SUM DOLLARS
   BY MONTHYEARSORT NOPRINT
   BY MONTHYEAR AS ''
WHERE YEAR EQ 1996;
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET ARGRAPHENGIN JSCHART
ON GRAPH SET GRWIDTH 1
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 3
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET AUTOFIT ON
END
-RUN


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Thanks for the feedback Vivian and Tony. A little disclaimer - I am a novice with coding so just learning context.

I want to provide a little more info for you. I have the fields computed based on a "createdate" prior to loading to my master file. Within my source is MonthYear, Month, Year, and MonthYearSort.

As far as how the dates display in the chart is flexible. Eventually I will be displaying a rolling 12 month of data so do need a month and year. Jan16 or Jan 2016 or any variation. I am open to all solutions including eliminating these fields from my master file and deriving them within my fex.

Thanks again for your help.

FIELDNAME=CREATEDATE, ALIAS=CreateDate, USAGE=HYYMDs, ACTUAL=HYYMDs,
MISSING=ON, $
FIELDNAME=MONTHYEAR, ALIAS=MonthYear, USAGE=A255V, ACTUAL=A255V,
MISSING=ON, $
FIELDNAME=MONTH, ALIAS=Month, USAGE=A255V, ACTUAL=A255V,
MISSING=ON, $
FIELDNAME=YEAR, ALIAS=Year, USAGE=A255V, ACTUAL=A255V,
MISSING=ON, $
FIELDNAME=MONTHYEARSORT, ALIAS=MonthYearSort, USAGE=A255V, ACTUAL=A255V,
MISSING=ON, $


7702, Windows7, html
 
Posts: 10 | Location: Minneapolis, Mn | Registered: February 27, 2013Report This Post
Master
posted Hide Post
If you know the values, you can also use COLUMNS..

ACROSS MONTHYEAR COLUMNS 'January2016' AND 'Feburary2016' AND 'March2016'



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Report This Post
Expert
posted Hide Post
I'd be inclined to use HDATE against CREATEDATE and get it into MtYY format per my example above.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Played with this all day and I can not get the chart to act any differently.... It either sorts across correctly by the MONTHYEARSORT field - 201601 - 201602 - 201603 and will not display the Month and year as desired or sorts incorrectly Feb2016 - Jan2016 - March2016.
This will be rolling months so can not hard code the columns


7702, Windows7, html
 
Posts: 10 | Location: Minneapolis, Mn | Registered: February 27, 2013Report This Post
Master
posted Hide Post
What is the format of your initial date field as stored in the database ?

Define file car
RPTDATE1/YYMD = &YYMD;
RPTDATE2/MDYY = RPTDATE1;
RPTDATE3/MtYY = RPTDATE2;
END

I recently had the identical problem where
I needed to display a date like May, 2015.
I learned, much to my delight, that only 1 ACROSS is allowed in a WF8 graPH.

YUK.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
If your initial database date is in HYYMDs format, you'll need to change the RPTDATE1 logic to use HDATE to extract the YYMD from the timestamp field. Then, you can use the RPTDATE2 and RPTDATE3 fields as I coded them.

Cheers!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
Actually, you can do it in one define -

DEFINE FILE filename
MONTHYEAR/MtYY = HDATE(CREATEDATE, 'YYMD');
....
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Tony I plugged that code in and it worked perfectly!! Thank You. One last question. I have some graphs that are using that MONTHYEAR field as a parameter in a drop down. How do I get those to sort correctly?


7702, Windows7, html
 
Posts: 10 | Location: Minneapolis, Mn | Registered: February 27, 2013Report This Post
Expert
posted Hide Post
How are you populating your drop down? From a synonym, external procedure or are you relying upon amper auto-prompting?

If from a synonym or auto-prompting then your best option would be to incorporate the define within your synonym. This would depend upon your data source as to how you would implement this. If it's a SQL source (as I suspect) then you just have to bear a few rules in mind.
  • The ALIAS must always be the column name as it appears in your SQL source
  • A date time format column can be redefined from an HYYMDs to one of the internal date formats (YYMD etc.) by changing the ACTUAL from HYYMDs etc. to DATE. The usage must be one of the internal date formats.


So changing your synonym to the following, you may be able to solve all of your issues with the current synonym -
FIELDNAME=CREATEDATE, ALIAS=CreateDate, USAGE=HYYMDs, ACTUAL=HYYMDs, MISSING=ON, $
FIELDNAME=MONTHYEAR, ALIAS=CreateDate, USAGE=MtYY, ACTUAL=DATE, MISSING=ON, $
$ this line is now commented out FIELDNAME=MONTHYEAR, ALIAS=MonthYear, USAGE=A255V, ACTUAL=A255V, MISSING=ON, $


If from an external procedure then you can just utilise the define that you already have or utilise one in the synonym.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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]Sorting Graph with NOPRINT field

Copyright © 1996-2020 Information Builders