DEFINE FILE EMPLOYEE
AYRMT/A6 = CHGDAT('A8YYMD', 'I6YYMD', EDIT(HIRE_DATE), 'A6');
GETMTH/A2=EDIT(AYRMT,'$$$$99');
GETYR/A2 =EDIT(AYRMT,'$$99$$');
FULLMTH/A3=DECODE GETMTH('01' Jan
'02' Feb
'03' Mar
'04' Apr
'05' May
'06' Jun
'07' Jul
'08' Aug
'09' Sep
'10' Oct
'11' Nov
'12' Dec ELSE 'XX');
AYR_MTH/A7=GETYR | ', ' | FULLMTH;
AMTH_YR/A7=FULLMTH | '''' | GETYR;
END
TABLE FILE EMPLOYEE
PRINT SALARY
DEPARTMENT
AYRMT
GETMTH
GETYR
AMTH_YR
ON TABLE HOLD AS HLD1
END


GRAPH FILE HLD1
SUM SALARY
BY DEPARTMENT
ACROSS AMTH_YR AS 'Months'
ACROSS GETMTH NOPRINT
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET UNITS PIXELS
ON GRAPH SET HAXIS 770.0
ON GRAPH SET VAXIS 405.0
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRLEGEND 1
ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setFillColor(getSeries(0),new Color(81,162,218));
setFillColor(getSeries(1),new Color(244,147,30));
setFillColor(getSeries(2),new Color(108,178,62));
setFillColor(getSeries(3),new Color(198,129,183));
setFillColor(getSeries(4),new Color(254,226,2));
setFillColor(getSeries(5),new Color(66,197,213));
setFillColor(getSeries(6),new Color(111,102,124));
setFillColor(getSeries(7), new Color(176,137,136));
setFillColor(getSeries(8), new Color(193,214,158));
setFillColor(getSeries(9),new Color(190,107,211));
*END
ENDSTYLE
END

This message has been edited. Last edited by: <Kathryn Henning>,

Apart from the incorrect year value that you have as a result of manipulating an A8YYMD output into an A6 (think of using A6YMD instead), have you ever looked at the various date format options that can be used, such as MtY?

If you sort alphanumeric representation of date values then expect the sort order to be incorrect. Using internal date formats (such as DMYY, DMY, DMtYY etc.) will sort correctly.

This is fairly basic training, so if you haven't had any then I would urge you to ask your management for some.


T

I have looked into more date options and used MtY format initially.But our requirement is to get like Jan'15 instead of Jan,15.
MtY will give you Jan,15 only.That's why we have tried changing that to character and achieved it.

If you have direct format in webfocus to get Jan'15 , then please provide that.

Note:
Our final requirement is to get Date format like Jan'15 in X axis of the graph with proper sorting order.

To fix the sort order, just swap your ACROSS columns.

Hi,
Thanks for the response. You mentioned to do like below ?

GRAPH FILE HLD1
SUM SALARY
BY DEPARTMENT
ACROSS GETMTH NOPRINT
ACROSS AMTH_YR AS 'Months'
ON GRAPH PCHOLD FORMAT JSCHART

I just tried swapping the NOPRINT ACROSSS column to first and PRINT ACROSS column to 2nd. But this is giving sorted month number (which I have mentioned as NOPRINT) in X axis instead of formatted date (Jan'15)

Also you're missing quotes in your decode.
FULLMTH/A3=DECODE GETMTH('01' 'Jan'
'02' 'Feb'
'03' 'Mar'
'04' 'Apr'
etc

Hi Darry,
Yes you are right. I didnt notice it.But that will not create any issue know.

Ah right, I had missed that this is a GRAPH request and not a TABLE request. Not much you can do about either the date-format or the sorting then.

What might help is to use an Advanced Graph with a doubly-scaled ordinal axis: one axis for years and one axis for months.

We have an internal example for that going like this:

GRAPH FILE HLD1
SUM SALARY

BY DEPARTMENT
BY Year
BY Month

ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 2

ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET UNITS CM
ON GRAPH SET HAXIS 944
ON GRAPH SET VAXIS 500
ON GRAPH PCHOLD FORMAT PNG
END

(I seem to recall that the legend column comes first, otherwise move the BY DEPARTMENT line to the bottom)

Hi,
I have tried providing advanced Graph option you mentioned and it is not working in my case.

How is it not working?

Here's a working example:

DEFINE FILE GGSALES
	Date1/YYMD	= DATECVT(DATE, 'I8YYMD', 'YYMD');
	Year/YY		= Date1;
	Month/Mt	= Date1;
END
GRAPH FILE GGSALES
SUM DOLLARS

BY Year
BY Month

ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2

ON GRAPH SET LOOKGRAPH VBRSTK1
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET 3D OFF
ON GRAPH SET GRID ON
ON GRAPH SET UNITS CM
ON GRAPH SET HAXIS 944
ON GRAPH SET VAXIS 500
ON GRAPH PCHOLD FORMAT PNG
END

All you need is add your departments into the mix.

I had a similar issue, but I converted my alpha to dates.

DEFINE FILE FOCCACHE/cdata
  DATEH/HMDYYS = HINPUT(22, COMMENTDATE, 8, 'HMDYYS');
END

TABLE FILE FOCCACHE/cdata
PRINT
	DATEONLY
	COMMENT
	COMMENTDATE
BY 	HIGHEST DATEH NOPRINT

This won't put an apostrophe in the x-axis labels, but converting the dates to date-times will allow you to remove the commas:

DEFINE FILE IBISAMP/EMPLOYEE
 HIREDATE/YYMD    = HIRE_DATE ;
 HIRE_DT_TM/HYYMD = HDTTM(HIREDATE,8,'HYYMD');
 HIRE_MONTH/HMtYY = HIRE_DT_TM ;
END
-*
GRAPH FILE IBISAMP/EMPLOYEE
 SUM SALARY
 BY DEPARTMENT
 ACROSS HIRE_MONTH AS 'Months'
-* ON GRAPH PCHOLD FORMAT JSCHART
 ON GRAPH SET VZERO OFF
 ON GRAPH SET HTMLENCODE ON
 ON GRAPH SET GRAPHDEFAULT OFF
 ON GRAPH SET UNITS PIXELS
 ON GRAPH SET HAXIS 770.0
 ON GRAPH SET VAXIS 405.0
 ON GRAPH SET GRMERGE ON
 ON GRAPH SET GRMULTIGRAPH 0
 ON GRAPH SET GRXAXIS 1
 ON GRAPH SET GRLEGEND 1
 ON GRAPH SET LOOKGRAPH VBRSTK1
 ON GRAPH SET AUTOFIT ON
 ON GRAPH SET STYLE *
*GRAPH_SCRIPT
 setFillColor(getSeries(0),new Color(81,162,218));
 setFillColor(getSeries(1),new Color(244,147,30));
 setFillColor(getSeries(2),new Color(108,178,62));
 setFillColor(getSeries(3),new Color(198,129,183));
 setFillColor(getSeries(4),new Color(254,226,2));
 setFillColor(getSeries(5),new Color(66,197,213));
 setFillColor(getSeries(6),new Color(111,102,124));
 setFillColor(getSeries(7), new Color(176,137,136));
 setFillColor(getSeries(8), new Color(193,214,158));
 setFillColor(getSeries(9),new Color(190,107,211));
*END
 ENDSTYLE
END

Pardon me if this is verbose. Just threw it together, keying off the example in Dan's post, above. Seems to get you what you want:

DEFINE FILE IBISAMP/EMPLOYEE
HIREDATE_MY/MY=DATECVT(HIRE_DATE,'I6YMD','MDY');
MONTHDISP/A6=DATETRAN(HIREDATE_MY,'(MY)','(tB)','EN',6,'A6');
MONTHDISP2/A6=CTRAN(6,MONTHDISP,32,39,'A6');
END
TABLE FILE IBISAMP/EMPLOYEE
BY HIRE_DATE NOPRINT
BY MONTHDISP2
ON TABLE SAVE
END
-RUN

-SET &COLUMN_ORD='';
-SET &IORETURN=0;
-REPEAT :BUILD_COL WHILE &IORETURN EQ 0;
-READ SAVE &MONTH.A6.
-IF &IORETURN NE 0 THEN GOTO :BUILD_COL;
-SET &COLUMN_ORD=IF &COLUMN_ORD EQ '' THEN '''' | &MONTH | '''' 
-                ELSE &COLUMN_ORD | ' AND ' | '''' | &MONTH | '''';
-:BUILD_COL

DEFINE FILE IBISAMP/EMPLOYEE
HIREDATE/YYMD    = HIRE_DATE ;
HIRE_DT_TM/HYYMD = HDTTM(HIREDATE,8,'HYYMD');
HIRE_MONTH/HMtYY = HIRE_DT_TM ;

HIREDATE_MY/MY=DATECVT(HIRE_DATE,'I6YMD','MDY');
MONTHDISP/A6=DATETRAN(HIREDATE_MY,'(MY)','(tB)','EN',6,'A6');
MONTHDISP2/A6=CTRAN(6,MONTHDISP,32,39,'A6');
END
GRAPH FILE IBISAMP/EMPLOYEE
 SUM SALARY
 BY DEPARTMENT
 ACROSS MONTHDISP2 AS 'Month' COLUMNS &COLUMN_ORD 
 ON GRAPH SET VZERO OFF
 ON GRAPH SET HTMLENCODE ON
 ON GRAPH SET GRAPHDEFAULT OFF
 ON GRAPH SET UNITS PIXELS
 ON GRAPH SET HAXIS 770.0
 ON GRAPH SET VAXIS 405.0
 ON GRAPH SET GRMERGE ADVANCED
 ON GRAPH SET GRMULTIGRAPH 0
 ON GRAPH SET GRXAXIS 1
 ON GRAPH SET GRLEGEND 1
 ON GRAPH SET LOOKGRAPH VBRSTK1
 ON GRAPH SET AUTOFIT ON
 ON GRAPH SET STYLE *
*GRAPH_SCRIPT
 setFillColor(getSeries(0),new Color(81,162,218));
 setFillColor(getSeries(1),new Color(244,147,30));
 setFillColor(getSeries(2),new Color(108,178,62));
 setFillColor(getSeries(3),new Color(198,129,183));
 setFillColor(getSeries(4),new Color(254,226,2));
 setFillColor(getSeries(5),new Color(66,197,213));
 setFillColor(getSeries(6),new Color(111,102,124));
 setFillColor(getSeries(7), new Color(176,137,136));
 setFillColor(getSeries(8), new Color(193,214,158));
 setFillColor(getSeries(9),new Color(190,107,211));
*END
 ENDSTYLE
END

The DATETRAN/CTRAN in the first step gets you the month and year separated by the quote. The Dialog Manager block of code after that builds the column order variable (&COLUMN_ORD), which is then used in the actual graph-creation step.

Craig

Hi Craig,
This is very useful, I will check this for our requirement and will update.

Hi ,
I used this logic and implemented in our application. Thanks a lot its working fine.

Thanks & Regards
Padmavathy Soorian