[SOLVED] Isuse in sorting of customized date format in Across
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 ENDThis message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS 7.6 Windows, All Outputs
July 01, 2015, 03:09 AM
Tony A
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
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
July 01, 2015, 03:54 AM
Padmavathy
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.
WebFOCUS 7.6 Windows, All Outputs
July 01, 2015, 04:34 AM
Wep5622
To fix the sort order, just swap your ACROSS columns.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
July 01, 2015, 05:39 AM
Padmavathy
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)
WebFOCUS 7.6 Windows, All Outputs
July 01, 2015, 05:56 AM
Darryl_uk
Also you're missing quotes in your decode. FULLMTH/A3=DECODE GETMTH('01' 'Jan' '02' 'Feb' '03' 'Mar' '04' 'Apr' etc
7.7.05 Windows.
July 01, 2015, 06:30 AM
Padmavathy
Hi Darry, Yes you are right. I didnt notice it.But that will not create any issue know.
WebFOCUS 7.6 Windows, All Outputs
July 01, 2015, 07:14 AM
Wep5622
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)
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
July 01, 2015, 07:47 AM
Padmavathy
Hi, I have tried providing advanced Graph option you mentioned and it is not working in my case.
WebFOCUS 7.6 Windows, All Outputs
July 01, 2015, 09:45 AM
Wep5622
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.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
July 01, 2015, 02:43 PM
GavinL
I had a similar issue, but I converted my alpha to dates.
- FOCUS Man, just FOCUS! ----------------------------- Product: WebFOCUS Version: 8.1.04 Server: Windows 2008 Server
July 01, 2015, 02:59 PM
Dan Satchell
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
WebFOCUS 7.7.05
July 02, 2015, 11:06 AM
Craig R.
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.