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 8105This message has been edited. Last edited by: <Emily McAllister>,
7702, Windows7, html
April 08, 2016, 02:45 AM
Vivian
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
April 08, 2016, 05:28 AM
Tony A
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
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
April 08, 2016, 10:24 AM
Frank Ronneng
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.
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
April 08, 2016, 11:12 AM
Tony A
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
April 08, 2016, 06:09 PM
Frank Ronneng
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
April 10, 2016, 04:09 PM
Tomsweb
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.
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.
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
April 11, 2016, 04:40 PM
Frank Ronneng
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
April 12, 2016, 08:24 AM
Tony A
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