Hello,

I want to get the subtotals calculated separately by CAR and one total row at COUNTRY level (Country ENGLAND I have chosen) . But, if u see the yellow highlighted request when used as multi verb request am getting the COUNTRY total as CAR individual SUB TOTALS. I have created a computed column that creates the % value which I wanted to see in the COUNTRY wise total row and not CAR SUBTOTAL ROW.

below is the code.

TABLE FILE CAR
PRINT *
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS CNTRY
END

TABLE FILE CNTRY
SUM 
COMPUTE DC/D12.1 = ((DEALER_COST * 10) /100); NOPRINT
BY COUNTRY NOPRINT

SUM 
DEALER_COST NOPRINT
BY COUNTRY NOPRINT
BY CAR NOPRINT

PRINT 
CAR DEALER_COST  
BY COUNTRY  
BY CAR

ON CAR SUBFOOT
"CAR <DEALER_COST"

ON COUNTRY SUBFOOT
"COUNTRY <TOT.DEALER_COST <<<<  <DC "

END
  

This message has been edited. Last edited by: <Kathryn Henning>,

Not sure if will answer your need, but the following two code does same result in two different way.

TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    COMPUTE PCT/D12.1% = ((DEALER_COST / RETAIL_COST) * 100);
BY COUNTRY
BY CAR
BY MODEL

ON CAR SUBTOTAL RECOMPUTE AS 'CAR TOT'
ON COUNTRY SUBTOTAL RECOMPUTE AS 'COUNTRY TOT'

WHERE COUNTRY EQ 'ENGLAND';
ON TABLE NOTOTAL
END

TABLE FILE CAR
SUM RETAIL_COST AS 'COUNTRY TOT RCOST'
    DEALER_COST AS 'COUNTRY TOT DCOST'
    COMPUTE PCT1/D12.1% = ((DEALER_COST / RETAIL_COST) * 100);
BY COUNTRY

SUM RETAIL_COST AS 'CAR TOT RCOST'
    DEALER_COST AS 'CAR TOT DCOST'
    COMPUTE PCT2/D12.1% = ((DEALER_COST / RETAIL_COST) * 100);
BY COUNTRY
BY CAR

PRINT MODEL RETAIL_COST DEALER_COST
BY COUNTRY  
BY CAR

WHERE COUNTRY EQ 'ENGLAND';
END

Hi Martin,

Thank you! for the code. I just modified little but and trying to bring the retail cost as subtotal using the subfoot. But if you see, CAR subfoot (subtotal) values I suppose to get by car
and ultimately by country. I'm getting country level total as even at car level.

if you take car "JAGUAR" I suppose to get 18621 where as I'm getting country level total i.e. 45319

Would there be anything I need to take care for this ?

TABLE FILE CAR
SUM RETAIL_COST AS 'COUNTRY TOT RCOST' NOPRINT
    DEALER_COST AS 'COUNTRY TOT DCOST' NOPRINT
    COMPUTE PCT1/D12.1% = ((DEALER_COST / RETAIL_COST) * 100);
BY COUNTRY

SUM RETAIL_COST AS 'CAR TOT RCOST' NOPRINT
    DEALER_COST AS 'CAR TOT DCOST'  
    COMPUTE PCT2/D12.1% = ((DEALER_COST / RETAIL_COST) * 100);
BY COUNTRY
BY CAR

PRINT MODEL RETAIL_COST DEALER_COST
BY COUNTRY  
BY CAR

ON CAR SUBFOOT
"CAR SUBFOOT <RETAIL_COST"
ON COUNTRY SUBFOOT
"COUNTRY SUBFOOT <RETAIL_COST"
WHERE COUNTRY EQ 'ENGLAND';
END
  

thank you so much!

Use column notation -

ON CAR SUBFOOT
"CAR SUBFOOT <C4"

C4 in this instance is the DEALER_COST AS 'CAR TOT DCOST' which is the 4th output column that is output e.g. when you count ignore NOPRINT fields.

Check out this help file

T

Since column notation can be confusing, I usually use DEFINEs to help eliminate confusion about which values are used in the different multi-verb levels and subsequent COMPUTEs and SUBFOOTs:

DEFINE FILE CAR
 RETAIL_COST_COUNTRY/D7 = RETAIL_COST ;
 DEALER_COST_COUNTRY/D7 = DEALER_COST ;
 RETAIL_COST_CAR/D7     = RETAIL_COST ;
 DEALER_COST_CAR/D7     = DEALER_COST ;
END
-*
TABLE FILE CAR
SUM RETAIL_COST_COUNTRY  NOPRINT
    DEALER_COST_COUNTRY  NOPRINT
    COMPUTE PCT1/D12.1% = ((DEALER_COST_COUNTRY / RETAIL_COST_COUNTRY) * 100);
BY COUNTRY

SUM RETAIL_COST_CAR  NOPRINT
    DEALER_COST_CAR  AS 'CAR TOT DCOST'  
    COMPUTE PCT2/D12.1% = ((DEALER_COST_CAR / RETAIL_COST_CAR) * 100);
BY COUNTRY
BY CAR

PRINT MODEL RETAIL_COST DEALER_COST
BY COUNTRY  
BY CAR

ON CAR SUBFOOT
 "CAR SUBFOOT <RETAIL_COST_CAR"
ON COUNTRY SUBFOOT
 "COUNTRY SUBFOOT <RETAIL_COST_COUNTRY"
WHERE COUNTRY EQ 'ENGLAND';
END

First of all thank you so much!!!

It worked very well for me... Dan, as you called that column notation is confusing yes! In my case I have around 20 fields which I have redefined and called in subtotals respectively. Thank you for your idea.

Tony - Your is simply superb!!! I think this column notation would be good if we have few or less columns. I now have two ways thank you!!!