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     Report - ON TABLE SUMMARIZE - Unable to do min of column?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Report - ON TABLE SUMMARIZE - Unable to do min of column?
 Login/Join
 
Rodney Chan
Member
posted
This has to be like one of the smallest things ever but i cant get it (really bugging me)

I have a report with a final line at the end where it gives the average (AVE) of the column

I have 

  ON TABLE SUMMARIZE
AVE. MINWT AS 'NEXT AVAILABLE'


Now, i want to simply change it to look for the MIN of that column but it doesnt work?

  ON TABLE SUMMARIZE
MIN. MINWT AS 'NEXT AVAILABLE'


I tried totalling, max(MAX) and average (AVE), count (CNT) of the column and they all work, i just cant use MIN

Is there a special command I am missing?

Thanks


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 26 | Registered: October 27, 2011Report This Post
Francis Mariani
Expert
posted Hide Post
None of the prefixes work very well if placed after ON TABLE SUMMARIZE (in v7.7.02):

TABLE FILE CAR
SUM
SALES
WHEELS
WEIGHT
BY COUNTRY
BY CAR
ON TABLE SUMMARIZE
CNT.WHEELS
END


That gives me a Total line with a value of 10 for each of the measures columns - not what you're expecting I imagine.

MIN and MAX put the maximum values of each column, even though I only specify one column.

I have to say I've never done this before...


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
SriAravind
Gold member
posted Hide Post
Rodney Chan - See the below code using MIN of the column which retrieving the minimum value. Is this the result you are looking for?

TABLE FILE CAR
SUM
DEALER_COST
BY CAR
ON TABLE SUMMARIZE
MIN.DEALER_COST AS 'MINDCOST'
END

CAR DEALER_COST
---- -------------
ALFA ROMEO 16,235
AUDI 5,063
BMW 49,500
DATSUN 2,626
JAGUAR 18,621
JENSEN 14,940
MASERATI 25,000
PEUGEOT 4,631
TOYOTA 2,886
TRIUMPH 4,292
----------------------------
MINDCOST 2,626
-----------------------------


WebFocus Version 7.7.05
Windows, HTML/PDF/EXL2K/AHTML
 
Posts: 63 | Registered: January 12, 2011Report This Post
Rodney Chan
Member
posted Hide Post
Hey Francis

Good to know its not just me but an IBI thing!

SriAravind yup thats what I am looking for.

After some further digging into my report, it seems that some values in the style sheet seem to be conflicting with the MIN value for i really dont know what reason.

If i left my style sheet as is i get the following

  TABLE FILE CAR
SUM
DEALER_COST
BY CAR
ON TABLE SUMMARIZE
MIN.DEALER_COST AS 'MINDCOST'
END

CAR DEALER_COST
---- -------------
ALFA ROMEO 16,235
AUDI 5,063
BMW 49,500
DATSUN 2,626
JAGUAR 18,621
JENSEN 14,940
MASERATI 25,000
PEUGEOT 4,631
TOYOTA 2,886
TRIUMPH 4,292
----------------------------
MINDCOST   0
-----------------------------


Very odd behaviour! I will talk to the end user and see if they want their pretty colors or if they want the right value Smiler

Though I did notice that if there are NULL values the MIN will take this into account. Would anyone know how i can exclude NULLs from being used in the MIN?

For example using the above code

  TABLE FILE CAR
SUM
DEALER_COST
BY CAR
ON TABLE SUMMARIZE
MIN.DEALER_COST AS 'MINDCOST'
END

CAR DEALER_COST
---- -------------
ALFA ROMEO 16,235
AUDI 5,063
BMW 49,500
DATSUN 2,626
JAGUAR 18,621
JENSEN 14,940
MASERATI 25,000
PEUGEOT 4,631
TOYOTA 2,886
TRIUMPH 4,292
RODNEY  
----------------------------
MINDCOST   0
-----------------------------


What if i still wanted to show 2,626 as the MIN and exclude the NULL?


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 26 | Registered: October 27, 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     Report - ON TABLE SUMMARIZE - Unable to do min of column?

Copyright © 1996-2020 Information Builders