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>,

Did you try changing the name of the monthyear field to something else? Perhaps WebFOCUS is assuming that this is a truncation of monthyearsort.

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

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, $

If you know the values, you can also use COLUMNS..

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

I'd be inclined to use HDATE against CREATEDATE and get it into MtYY format per my example above.

T

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

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.

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!

Actually, you can do it in one define -

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

T

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?

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