Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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?
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]How to use a column total in a compute?
 Login/Join
 
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, 2019Reply With QuoteReport This Post
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: 788 | Registered: April 23, 2003Reply With QuoteReport This Post
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, 2019Reply With QuoteReport This Post
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: 1821 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
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, 2019Reply With QuoteReport This Post
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: 1949 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
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, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.