Hi,

I am having trouble finding a way to add a total to a BY TOTAL column. Let me show you the code first

TABLE FILE CAR
SUM
RETAIL_COST
BY TOTAL RETAIL_COST
BY CAR
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

With the code above, it gives the column total across the Country but there is no column total in RETAIL_COST. How to add a column total to the RETAIL_COST?

I have tried to use ROW-TOTAL, but it will give the column total in the RETAIL_COST, but the problem is that it will also move the column to the very end (the right side) of the report which is not the format I want. Can anyone give me some suggestions?

Thanks in advance for your help.

Does this work for you?

TABLE FILE CAR
SUM RETAIL_COST 
BY TOTAL RETAIL_COST NOPRINT
BY CAR
SUM 
RETAIL_COST 
BY TOTAL RETAIL_COST NOPRINT
BY CAR
ACROSS COUNTRY 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

Hi Ginny,

Thank you so very much for your solution. I have tried it in my code, and it works exactly the way i want it. Is there a way to add ranking to the report?

quote:

TABLE FILE CAR
SUM RETAIL_COST
BY TOTAL RETAIL_COST NOPRINT
BY CAR
SUM
RETAIL_COST
BY TOTAL RETAIL_COST NOPRINT
BY CAR
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

What do you want to rank? And in what order?

Hi Ginny,

I would like to rank the RETAIL_COST from highest to lowest.. i have played around with your code but still can't find a way to do that.. It would be a big help if you could find the solution. Thanks in advance for your help.

Simon

My initial forays into a solution haven't been successful. The BY TOTAL kind of messes things up. I will keep playing but an easy answer would be to put the initial results into a hold file then rank them.

Yes, I was thinking about putting the data in the hold file. But don't have enough experience in FOC program to do that...Hope you will have some time to find a solution. I do very much appreciate.

Is this what you want?

 

TABLE FILE CAR
SUM RETAIL_COST 
BY HIGHEST TOTAL RETAIL_COST  
BY CAR
ON TABLE HOLD AS HOLDCAR1
END


TABLE FILE HOLDCAR1
PRINT E03 
COMPUTE RNK/I3=RNK +1;
BY HIGHEST E01
BY CAR
ON TABLE HOLD AS HOLDCAR2
END

TABLE FILE CAR
SUM RETAIL_COST
BY CAR
BY COUNTRY
ON TABLE HOLD AS HOLDCAR3 FORMAT FOCUS INDEX CAR
END

JOIN CAR IN HOLDCAR2 TO ALL CAR IN HOLDCAR3
TABLE FILE HOLDCAR2
SUM 
HOLDCAR3.RETAIL_COST 
BY RNK AS RANK 
  
BY CAR
ACROSS COUNTRY ROW-TOTAL
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END 
 

If you don't need the rank to print, you can use Ginny's code and change the BY TOTAL to BY HIGHEST TOTAL

Thanks, Pat.

Here's another way:

TABLE FILE CAR
SUM RETAIL_COST 
BY TOTAL RETAIL_COST NOPRINT
BY CAR
SUM 
RETAIL_COST 
BY TOTAL RETAIL_COST NOPRINT
BY CAR
ACROSS COUNTRY 
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE CAR
BY COUNTRY
ON TABLE SAVE
END
-SET &LIM=&LINES
TABLEF FILE HOLD
PRINT RETAIL_COST
-REPEAT ENDLOOP1 &LIM TIMES
-READ SAVE NOCLOSE  &COUNTRY.A10.
'RET&COUNTRY' AS '&COUNTRY'
-ENDLOOP1
-CLOSE SAVE
COMPUTE RANK/I6=RANK+1; 
BY CAR
ON TABLE COLUMN-TOTAL AS 'TOTAL' RETAIL_COST 
-REPEAT ENDLOOP2 &LIM TIMES
-READ SAVE NOCLOSE  &COUNTRY.A10.
'RET&COUNTRY' 
-ENDLOOP2
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=N8, SEQUENCE=1,$
ENDSTYLE
END

Hi Pat, thank you for the solution. I just ran your code and it did show the rank as well as the column total for the RETAIL_COST which i have been hoping to acheive. Is there a way to move the RETAIL_COST column from the last column to the third column which is next to CAR column?

Ginny, I also ran your code and got an error message saying


(FOC003) THE FIELDNAME IS NOT RECOGNIZED: RETENGLAND
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

I don't know how to fix it. Could you help me to fix it?

I do very much appreciate both of you for the time and help.

Oops, I neglected to copy and paste this line at the beginning of the code:

SET ASNAMES=ON

Another sample

TABLE FILE CAR
SUM RETAIL_COST AS 'Total'
BY HIGHEST TOTAL RETAIL_COST NOPRINT
BY CAR
SUM
RETAIL_COST
BY TOTAL RETAIL_COST NOPRINT
BY CAR
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

Hi Ginny,

Yes, your solution is exactly what I am looking for. Thank you so very much for your help.

AG and Pat, Thank you for your suggestions too. I very much appreciate it.

Simon

quote:

SET ASNAMES=ON