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]How to use a column total in a compute?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]How to use a column total in a compute?
 Login/Join
 
Kort Thompson
Member
posted
My task is to create the following compute in my query but I keep getting an error. I'm trying to multiply the sum of "dealer cost" x "sales" divided by the column total for "sales". This is the query i'm trying to use along with the error i receive:

DEFINE FILE CAR
SALES_1/D6 = SALES
END

-RUN

SET EXCELSERVURL =''

TABLE FILE CAR
SUM
DEALER_COST AS 'Dealer Cost'
RETAIL_COST AS 'Retail Cost'
SALES_1 AS 'Units Sold'

COMPUTE DEALER_COSTS/D8= (DEALER_COST * SALES_1) / COLUMN TOTAL SALES_1 ; AS 'FG Goal'

BY CAR AS 'Car'
BY MODEL AS 'Model'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SUBTOTAL
END
-RUN


0 ERROR AT OR NEAR LINE 40 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC224) SYNTAX ERROR: TOTAL
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

This message has been edited. Last edited by: Kort Thompson,


KW

WF Versions: DEV 8105 OS: Windows Outputs: HTML, Excel
In WebFOCUS since March '19
 
Posts: 11 | Registered: March 18, 2019Report This Post
David Briars
Master
posted Hide Post
One way to calculate and operate upon totals is to use a multi-set request:

DEFINE FILE CAR
 UNITS_SOLD/D6 = SALES
END
TABLE FILE CAR
 SUM
  UNITS_SOLD  NOPRINT
 SUM
  DEALER_COST AS 'Dealer Cost'
  RETAIL_COST AS 'Retail Cost'
  UNITS_SOLD  AS 'Units Sold'
 COMPUTE 
  DEALER_COSTS/D8= (DEALER_COST * UNITS_SOLD) / C1 ; AS 'FG Goal'
 BY CAR AS 'Car'
 BY MODEL AS 'Model'
 ON TABLE SUBTOTAL
END  

  Car               Model                     Dealer Cost  Retail Cost  Units Sold     FG Goal
  ---               -----                     -----------  -----------  ----------     -------
  ALFA ROMEO        2000 4 DOOR BERLINA             4,915        5,925       4,800         113
  ...


Another way would be to use prefix operators.

I tend to use a multi-set request as I can see the values I am working with during development, and then NOPRINT them before releasing to production.
 
Posts: 822 | Registered: April 23, 2003Report This Post
Kort Thompson
Member
posted Hide Post
Thank you This solution works and solved the issue. I do have another question about the compute you used: Where did you get C1 as the divisor. I don't understand where that came from?


KW

WF Versions: DEV 8105 OS: Windows Outputs: HTML, Excel
In WebFOCUS since March '19
 
Posts: 11 | Registered: March 18, 2019Report This Post
BabakNYC
Virtuoso
posted Hide Post
C1 is Column 1 in your report.

https://webfocusinfocenter.inf...lang/source/ctf5.htm

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Kort Thompson
Member
posted Hide Post
Also, what prefix operator would allow me to divide by the column total?


KW

WF Versions: DEV 8105 OS: Windows Outputs: HTML, Excel
In WebFOCUS since March '19
 
Posts: 11 | Registered: March 18, 2019Report This Post
Danny-SRL
Virtuoso
posted Hide Post
 
DEFINE FILE CAR
 UNITS_SOLD/D6 = SALES;
END 
TABLE FILE CAR
 SUM
  DEALER_COST AS 'Dealer Cost'
  RETAIL_COST AS 'Retail Cost'
  UNITS_SOLD  AS 'Units Sold'
 COMPUTE 
  DEALER_COSTS/D8= (DEALER_COST * UNITS_SOLD) / TOT.UNITS_SOLD ; AS 'FG Goal'
 BY CAR AS 'Car'
 BY MODEL AS 'Model'
 ON TABLE SUBTOTAL
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Kort Thompson
Member
posted Hide Post
Thank you for the prefix advice. Using a prefix was more applicable in this specific case but still learned a lot from all the support provided in this thread so thanks to all.


KW

WF Versions: DEV 8105 OS: Windows Outputs: HTML, Excel
In WebFOCUS since March '19
 
Posts: 11 | Registered: March 18, 2019Report 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]How to use a column total in a compute?

Copyright © 1996-2020 Information Builders