[SOLVED]How to use a column total in a compute?

February 28, 2020, 09:45 AM

Kort Thompson

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,

February 28, 2020, 10:07 AM

David Briars

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.

February 28, 2020, 10:33 AM

Kort Thompson

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?

February 28, 2020, 10:42 AM

BabakNYC

C1 is Column 1 in your report.

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

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

February 28, 2020, 10:51 AM

Kort Thompson

Also, what prefix operator would allow me to divide by the column total?

March 01, 2020, 06:00 AM

Danny-SRL

 
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

March 01, 2020, 10:51 AM

Kort Thompson

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.