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     [SOLVED]SUM then BY, unexpected output

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]SUM then BY, unexpected output
 Login/Join
 
Charles Richards
Platinum Member
posted
I am trying to list some itemcodes from the ones that make the most profit to the least BY DEPARTMENT and BY CLASS... I sum the items BY ITMECODE becuase some of the items were ordered more then once. After I get the Total profit column and try to add a BY TOTALPROFIT column it seems to be puting them in the order of the highest totalprofit before they were summed...

EX.
before sum
BY TOTALPROFIT
ITEMCODE___#ofOrders____TOTALPROFIT
12346______1____________2.00
12346______1____________2.00
12346______1____________2.00
12347______1____________1.75
12347______1____________1.75
12347______1____________1.75
12345______1____________1.25
12345______1____________1.25
12345______1____________1.25
12345______1____________1.25
12345______1____________1.25


after SUM
then BY TOTALPROFIT
ITEMCODE___#ofOrders____TOTALPROFIT
12346______3____________6.00
12347______3____________5.25
12345______5____________6.25

I believe it is because there original values would put them in this order

I don't know how to put them in the correct order after they are summed

Thanks for the help!

This message has been edited. Last edited by: Charles Richards,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
FILEDEF DATAMAST DISK DATA1.MAS
-RUN

-WRITE DATAMAST FILE=DATA1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=ITEMCODE   , ALIAS=E01, USAGE=A5    , ACTUAL=A5, $
-WRITE DATAMAST   FIELD=FILL1      , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=#ofOrders  , ALIAS=E02, USAGE=I4    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=FILL2      , ALIAS=   , USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=TOTALPROFIT, ALIAS=E03, USAGE=P10.2 , ACTUAL=A4, $

FILEDEF DATA1 DISK DATA1.FTM
-RUN
-WRITE DATA1 12346 1 2.00
-WRITE DATA1 12346 1 2.00
-WRITE DATA1 12346 1 2.00
-WRITE DATA1 12347 1 1.75
-WRITE DATA1 12347 1 1.75
-WRITE DATA1 12347 1 1.75
-WRITE DATA1 12345 1 1.25
-WRITE DATA1 12345 1 1.25
-WRITE DATA1 12345 1 1.25
-WRITE DATA1 12345 1 1.25
-WRITE DATA1 12345 1 1.25
-RUN

TABLE FILE DATA1
SUM
#ofOrders
TOTALPROFIT
BY TOTAL HIGHEST TOTALPROFIT NOPRINT
BY ITEMCODE
END
-RUN


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
Charles Richards
Platinum Member
posted Hide Post
Thank you so much! all i needed was to TOTAL before HIGHEST

in the line

BY TOTAL HIGHEST TOTOALPROFIT


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
Nice and quick, eh?


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
Charles Richards
Platinum Member
posted Hide Post
Yea thanks so much, Your the Man!


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 111 | Registered: May 12, 2011Report 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     [SOLVED]SUM then BY, unexpected output

Copyright © 1996-2020 Information Builders