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     [CLOSED] How can I sort months

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] How can I sort months
 Login/Join
 
Member
posted
How can I sort months (jan,feb,march...december). Thanks in advance

I try this.
TABLE FILE SPECIAL_REPORT
PRINT
TOTAL_RECORDS
BY EACH_MONTH

But the result is not what i inspected.

EACH_MONTH TOTAL_RECORDS
APRIL 9023
AUGUST 8083
FEBRUARY 7215
JANUARY 9530
JULY 7490
JUNE 7330
MARCH 7041
MAY 8922
SEPTEMBER 8437

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


Webfocus 7.7.02, Win 7,Pdf, Excel, HTML
 
Posts: 25 | Location: VA | Registered: September 10, 2011Report This Post
Expert
posted Hide Post
Create a DEFINE or COMPUTE column that is a date formatted column based on your alpha column, then us it as a BY NOPRINT before your alpha column.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Even better - read the following:

Describing Data With WebFOCUS Language > Describing an Individual Field > Converting Alphanumeric Dates to WebFOCUS Dates

Alan B has mentioned this a few times: Converting A10V to MDYY

Let us know what the format of EACH_MONTH is.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
You would benefit from the (almost) 1001 Ways to Work with Dates available PDF from www.aviter.com.

It has TONS of info on manipulating dates.

Good luck.

Vivian


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
Platinum Member
posted Hide Post
As Francis said you need to carry the month numbers as otherwise you get sorted by ABC's. Usually you have the month as a number field in the database so you carry say MONTH_NR and MONTH_NM and use the MONTH_NR as the BY NOPRINT field.

Depending on how your DB is set up usually you have a translation table for the date formats - if not that might be an idea as it saves a lot of coding. Or then you create a module you can use as INCLUDE that does the most usual and annoying format changes if the DBA's don't want to play ball.


Cheers,
H.

WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 115 | Location: Brighton UK | Registered: February 19, 2005Report This Post
Member
posted Hide Post
DBA provide me this. each_month is a string

Table special_report
====================
each_month, total_records
--------------------

each_month varchar(30)
total_records int(4)

MASTER FILE:
FILENAME=SPECIAL_REPORT, SUFFIX=SQLMYSQL, $
SEGMENT=SPECIAL_REPORT, SEGTYPE=S0, $
FIELDNAME=EACH_MONTH, ALIAS=each_month, USAGE=A30V, ACTUAL=A30V,
MISSING=ON, $
FIELDNAME=TOTAL_RECORDS, ALIAS=total_records, USAGE=I11, ACTUAL=I4,
MISSING=ON, $
FIELDNAME=SEQ, ALIAS=seq, USAGE=I11, ACTUAL=I4,
MISSING=ON, $


Webfocus 7.7.02, Win 7,Pdf, Excel, HTML
 
Posts: 25 | Location: VA | Registered: September 10, 2011Report This Post
Expert
posted Hide Post
Unfortunately, it looks like DATEPATTERN cannot be used with a field that has only month in it, so here's a quick way to solve this:
DEFINE FILE FP_DTIC_SPECIAL_REPORT
EACH_MONTH_SORT/I2 = DECODE EACH_MONTH(
'JANUARY' 01, 'FEBRUARY' 02, 'MARCH'     03,
'APRIL'   04, 'MAY'      05, 'JUNE'      06,
'JULY'    07, 'AUGUST'   08, 'SEPTEMBER' 09,
'OCTOBER' 10, 'NOVEMBER' 11, 'DECEMBER'  12, ELSE 99);
END
 
TABLE FILE FP_DTIC_SPECIAL_REPORT
SUM TOTAL_RECORDS
BY EACH_MONTH_SORT NOPRINT
BY EACH_MONTH
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
Thank you all, now I understood the concept.

Dave.


Webfocus 7.7.02, Win 7,Pdf, Excel, HTML
 
Posts: 25 | Location: VA | Registered: September 10, 2011Report This Post
Virtuoso
posted Hide Post
DATEPATTERN should work for month only:
FILENAME=MONTHSALES, SUFFIX=SQLMYSQL, $
  SEGMENT=MONTHSALES, SEGTYPE=S0, $
    FIELDNAME=ID, ALIAS=id, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=MONTH, ALIAS=Month, USAGE=Mtr, ACTUAL=A12V,
      MISSING=ON,DATEPATTERN='[MONTH]', $
    FIELDNAME=SALES, ALIAS=Sales, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $

However it is not required, as WebFOCUS is intelligent enough to work with direct translation of dates,e.g.:
FILENAME=MONTHSALES, SUFFIX=SQLMYSQL, $
  SEGMENT=MONTHSALES, SEGTYPE=S0, $
    FIELDNAME=ID, ALIAS=id, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=MONTH, ALIAS=Month, USAGE=Mtr, ACTUAL=A12V,
      MISSING=ON, $
    FIELDNAME=SALES, ALIAS=Sales, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $

Both give:
TABLE FILE MONTHSALES
SUM SALES
BY MONTH
END

MONTH	  SALES
January	  16
February  22
March	  27
April	  36
May	  21
June	  32
August	  36
September 12
October	  11
November  29
December  43

From:
TABLE FILE MONTHSALES
PRINT ID MONTH SALES
END


ID	 MONTH	   SALES
1	 September 12
2	 October   11
3	 June	   32
4	 May	   21
5	 April	   17
6	 January   16
7	 February  22
8	 December  43
9	 November  29
10	 March	   27
11	 August	   36
12	 April	   19


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
That's a revelation - thanks Alan.

Too bad Mtr is not consistently recognized. This won't work:
COMPUTE EACH_MONTH2/Mtr = LAST EACH_MONTH;
COMPUTE MTH_DIFF/I2 = DATEDIF(EACH_MONTH, EACH_MONTH2, 'M');


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
I tried Alan's solution on my oracle dates table but it only works if I am using PRINT.

SUM only works if I let WebFOCUS do the aggregation by issuing the ENGINE SQLORA SET OPTIMIZATION NOAGGR command.

e.g.

TABLE FILE DATES
PRINT
DAY
BY MONTH
END

will work

TABLE FILE DATES
SUM
CNT.DAY
BY MONTH
END

returns the following error:

(FOC1400) SQLCODE IS 1722 (HEX: 000006BA)
: ORA-01722: invalid number
(FOC1407) SQL FETCH CURSOR ERROR. : DATES

Are there some database limitations to using this?


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
Virtuoso
posted Hide Post
Interesting. I've only used this on MySQl and SQL Server with 7.703 and above. So possible, and obviously, there are limitations. Is this using DATEPATTERN?


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
The oracle table has a column with varchar2(3) format called CALENDARMONTHSHORTNAME, the data is like Jan, Feb etc.

I defined a column that looks like this in the master file:

FIELDNAME=MONTHSHORT, ALIAS=CALENDARMONTHSHORTNAME, USAGE=Mt, ACTUAL=A3,
MISSING=ON,DATEPATTERN='[Mon]', $

I also tried it without DATEPATTERN in the definition and the behaviour is the same for both. Works if its PRINT, doesn't if its SUM.


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report 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     [CLOSED] How can I sort months

Copyright © 1996-2020 Information Builders