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] Sort date(MM-DD-YY) in ascending order

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Sort date(MM-DD-YY) in ascending order
 Login/Join
 
Suji
Gold member
posted
Hi,
My requirement is to sort dates which are in alphanumeric format in ascending order. I am facing an issue especially when I have dates (say 12-01-04,01-01-05). The dates are sorted in this order 01-01-05, 12-01-04). It should be sorted in the ascending order from the year 04 to 05.

TABLE FILE SQLOUT
PRINT
metric1/D16.4
attr2
AND COMPUTE
COMPUTE TRANSDATE/YYMD = HDATE(attr2, 'YYMD');
TIMELINE1/I6MDY =DATECVT(TRANSDATE, 'YYMD', 'I6MDY');
TIMELINE/A8=EDIT(TIMELINE1,'99-99-99');
ON TABLE HOLD
END

GRAPH FILE HOLD
SUM
metric1/D16.4 AS ''
ACROSS TIMELINE AS ''
ON GRAPH SET 3D OFF
ON GRAPH SET STYLE *
TYPE=DATA, ACROSSCOLUMN=metric1, COLOR=RGB(154 51 255), $
ENDSTYLE
END

Can anyone please suggest a solution for this issue?

Thanks,
Suji.

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.7.02(Production)
WebFOCUS 7.7.02(Test region)
Oracle backend (10g)and Windows Frontend.
HTML,PDF,EXL2K,FLEX,AHTML
 
Posts: 68 | Registered: June 07, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Use a formatted Smart Date and your dates should sort correctly in the graph.

COMPUTE TRANSDATE/YYMD = HDATE(attr2, 'YYMD');
COMPUTE TIMELINE/M-D-Y = TRANSDATE ;


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Suji
Gold member
posted Hide Post
Yes. It is working fine. I have another question.
COMPUTE TIMELINE/MDY = TRANSDATE ;
I need to get date values in this format '01232005' and they have to be sorted in ascending order. But I dont want to have '/' slashes in between the dates. Can you please suggest a solution .

Thanks,
Suji.


WebFOCUS 7.7.02(Production)
WebFOCUS 7.7.02(Test region)
Oracle backend (10g)and Windows Frontend.
HTML,PDF,EXL2K,FLEX,AHTML
 
Posts: 68 | Registered: June 07, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Try this:

COMPUTE TIMELINE/M|D|YY = TRANSDATE ;


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
"M|D|YY" hmmm, very interesting!

Or, sort by a date field but display the alhpa field:

TABLE FILE SQLOUT
PRINT 
metric1/D16.4 
attr2
AND COMPUTE
COMPUTE TRANSDATE/YYMD = HDATE(attr2, 'YYMD');
TIMELINE1/I6MDY =DATECVT(TRANSDATE, 'YYMD', 'I6MDY');
TIMELINE/A8=EDIT(TIMELINE1,'99-99-99');
ON TABLE HOLD
END

GRAPH FILE HOLD
SUM 
metric1/D16.4 AS ''
ACROSS TRANSDATE NOPRINT
ACROSS TIMELINE AS ''
ON GRAPH SET 3D OFF
ON GRAPH SET STYLE *
TYPE=DATA, ACROSSCOLUMN=metric1, COLOR=RGB(154 51 255), $
ENDSTYLE
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
Suji
Gold member
posted Hide Post
Hi,
Can someone suggest a solution for implementing date in the format 'MD'? I tried using 'M|D' . But it is throwing an error.

Thanks,
Suji.


WebFOCUS 7.7.02(Production)
WebFOCUS 7.7.02(Test region)
Oracle backend (10g)and Windows Frontend.
HTML,PDF,EXL2K,FLEX,AHTML
 
Posts: 68 | Registered: June 07, 2007Report This Post
Suji
Gold member
posted Hide Post
Hi,
Can someone suggest a solution for displaying date in the formats (MD and Mt-Y)?

For example,
1224,1230 (MD Format)
Jan-09,Dec-09,Jan-10 (Mt-Y format)

Thanks,
Suji.


WebFOCUS 7.7.02(Production)
WebFOCUS 7.7.02(Test region)
Oracle backend (10g)and Windows Frontend.
HTML,PDF,EXL2K,FLEX,AHTML
 
Posts: 68 | Registered: June 07, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
This assumes your attr2 date is in DATE-TIME format.

DATEMD/HMDN   = attr2 ;
DATEMTY/HMtY- = attr2 ;


This link provides detailed info about formatting DATE-TIME fields:

http://documentation.informati..._desdat/04dd14_2.htm

This link provides detailed info about formatting Smart Dates:

http://documentation.informati...rts436/1_aarpt10.htm


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Suji
Gold member
posted Hide Post
Dan,
DATEQY/HQY- = attr2 ;
I want to specify the quarterly information of the year. But this syntax is not working.(say Q1-2009,Q2-2010 etc).

Thanks,
Suji.


WebFOCUS 7.7.02(Production)
WebFOCUS 7.7.02(Test region)
Oracle backend (10g)and Windows Frontend.
HTML,PDF,EXL2K,FLEX,AHTML
 
Posts: 68 | Registered: June 07, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Suji,

Please specify exactly which date format you would like. It started off with MDY and has now evolved to HQY.

There are several types of date formats, with functions and formats particular to each.

What is the input field format and what is the format you would like?


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
Dan Satchell
Virtuoso
posted Hide Post
Since date-time formats do not support quarterly output, you will have to convert your date-time value to a Smart Date.

TRANSDATE/YYMD = HDATE(attr2, 'YYMD');
DATEQY/Q-YY    = TRANSDATE ;

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Dan,

It was a real surprise to me that HYYQ is not supported, yet the word "quarter" is defined as a component of date-time fields.


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
Dan Satchell
Virtuoso
posted Hide Post
Francis,

Yes, for me too! And function HADD can be used to add/subtract a quarter, and HPART and HNAME to extract the quarter component.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Well then, I'm opening a case to get an explanation!


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
Francis Mariani
Expert
posted Hide Post
Apparently it will take a New Feature Request to add HYYQ and HYQ format to WebFOCUS Date-Time fields, so open a case with Tech Support.


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
  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] Sort date(MM-DD-YY) in ascending order

Copyright © 1996-2020 Information Builders