How do I calculate total using an specific formula to specific columns?

This message has been edited. Last edited by: Kathleen Butler,

Using a COMPUTE as for the formula and RECOMPUTE as in the TOTAL command line option.

TABLE FILE CAR
SUM RETAIL_COST
    DEALER_COST
    COMPUTE RATE/D6.2% = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE COLUMN-TOTAL AS 'Total' RECOMPUTE
END
-RUN

I need to calculate subtotals using specific formula to each column.

This is my report:

 
TABLE FILE H2X
SUM
-* SEGMENT SHARE

COMPUTE PRIOR_PCT_SHARE/D4.1% = (PRIOR_RENAVAM*100 ) /PRIOR_SUBSEG; AS 'Prior FM'
COMPUTE PCT_RENAVAM_SHARE_DAY/D4.1% = (QT_RENAVAM_DAY*100)/QT_RENAVAM_SUBSEG_DAY; AS 'Daily Share'
COMPUTE PCT_OBJ_SHARE/D4.1% = (DST.OBJECTIVE*100 ) /SUBSEG; AS '&OBJ_REPORT1.EVAL'
COMPUTE DIF_SHARE/D4.2 = PCT_RENAVAM_SHARE_DAY - PCT_OBJ_SHARE; AS 'Daily vs Obj.'
COMPUTE PCT_RENAVAM_SHARE/D4.1% =(RENAVAM*100) / SUBSEG; AS '&MONTH_REPORT.EVAL'
COMPUTE DIF_SHARE2/D4.2 = PCT_RENAVAM_SHARE - PCT_OBJ_SHARE; AS 'MTD vs Obj.'

-* INDUSTRY SEGMENTATION

COMPUTE PRIOR_PCT_IND_SEG/D4.1% = (PRIOR_SUBSEG*100 ) /PRIOR_TOTAL_IND;  AS 'Prior FM'
COMPUTE PCT_OBJ_IND_SEG/D4.1% = (OBJECTIVE_SUBSEG*100 ) /DST.OBJECTIVE_IND;  AS '&OBJ_REPORT1.EVAL'
COMPUTE PCT_RENAVAM_IND_SEG/D4.1% =(SUBSEG*100) / TOTAL_INDUSTRY;  AS '&MONTH_REPORT.EVAL'
COMPUTE DIF_IND_SEG/D4.2 = PCT_RENAVAM_IND_SEG - PCT_OBJ_IND_SEG; AS 'MTD vs Obj.'

-* INDUSTRY SHARE
COMPUTE PRIOR_PCT_IND/D4.1% = (PRIOR_FM*100 ) /PRIOR_TOTAL_IND; AS 'Prior FM'
COMPUTE PCT_OBJ_IND/D4.1% = (OBJECTIVE*100 ) /DST.OBJECTIVE_IND;   AS '&OBJ_REPORT1.EVAL'
COMPUTE PCT_RENAVAM_IND_DAY/D4.1% = (QT_RENAVAM_DAY*100)/QT_RENAVAM_IND_DAY; AS 'Daily Share'
COMPUTE DIF_DAILY_IND/D4.2 = PCT_RENAVAM_IND_DAY - PCT_OBJ_IND; AS 'Daily vs Obj.'
COMPUTE PCT_RENAVAM_IND/D4.1% =(RENAVAM*100) / TOTAL_INDUSTRY;  AS '&MONTH_REPORT.EVAL'
COMPUTE DIF_DAILY_IND2/D4.2 = PCT_RENAVAM_IND - PCT_OBJ_IND; AS 'MTD vs Obj.'


PRIOR_FM AS 'Prior FM'
DST.OBJECTIVE AS 'OBJ.'
RENAVAM AS '&MONTH_REPORT.EVAL'
COMPUTE VAR/I5C = ( RENAVAM - OBJECTIVE ); AS '(+/-)Obj.'
BY SEGMENT NOPRINT
BY MODEL_REPORT AS ''
ON TABLE PCHOLD FORMAT EXL2K
ON SEGMENT  SUMMARIZE  PRIOR_FM OBJECTIVE RENAVAM VAR AS 'TOTAL'
ON TABLE SUBFOOT
...
END
 

I need something to apply specific calculations, like subtotals ON SEGMENT but it´s specific calculations to each columns, like this: ( JUST HOW I WISH I COULD USE IT)

ON SEGMENT SUMMARIZE CASE
WHEN PRIOR_PCT_SHARE AND VALUE EQ 'CARS' THEN (TOTAL_CARS*100)/TOTAL_INDUSTRY;
WHEN PRIOR_PCT_SHARE AND VALUE EQ 'TRUCKS' THEN (TOTAL_TRUCKS*100)/TOTAL_INDUSTRY;
WHEN PCT_RENAVAM_SHARE_DAY AND VALUE EQ 'CARS' THEN (TOTAL_CARS_DAY*100)/TOTAL_INDUSTRY_DAY;
....


Any suggestions?

Find (and read) the documentation on the RECAP command!
That will definitely help you to get you to where you want to be!

Ok, I changed my code to use RECAP but I am facing some issues on second recap.
Can anyone give me a hand?

TABLE FILE H2X
SUM
PRIOR_FM AS 'Prior FM'
DST.OBJECTIVE AS 'OBJ.'
RENAVAM AS '&MONTH_REPORT.EVAL'
COMPUTE VAR/I5C = ( RENAVAM - OBJECTIVE ); AS '(+/-)Obj.'

FOR MODEL_REPORT
-*********************
-* CARS GOES HERE
-**********************
-* NOVO ZZZ
'ZZZ - B HATCH'								AS				'Novo ZZZ - B Hatch'					LABEL C1	OVER
'ZZZ + - B SEDAN/SW'							AS				'Novo ZZZ - B Sedan/SW'				LABEL C2	OVER

-* NEW ZZZZZ
'ZZZZZ ROCAM HATCH - B HATCH'				OR
'OTHERS - CARS - B HATCH'					AS				'New ZZZZZ Hatch BR - B Hatch'		LABEL C3	OVER
'ZZZZZ ROCAM SEDAN - B SEDAN/SW' 			OR
'NEW ZZZZZ SEDAN - B SEDAN/SW'				AS				'New ZZZZZ Sedan - B Sedan/SW'		LABEL C4	OVER

-* FOCUS
'OLD FOCUS HATCH - C HATCH'					OR
'OTHERS - CARS - C HATCH'					AS				'Focus Hatch - C Hatch'				LABEL C5	OVER
'OLD FOCUS SEDAN - C SEDAN/SW'				OR
'OTHERS - CARS - C SEDAN/SW'				AS				'Focus Sedan - C Sedan/SW'			LABEL C6	OVER

-*CARRAO
'CARRAO - CD'								OR
'OTHERS - CARS - CD'						AS 				'CARRAO - CD'						LABEL C7	OVER

-*OTHER CARS
'OTHERS - CARS - SPORT'						AS				'Other Cars'						LABEL C8	OVER	

BAR																											OVER
RECAP TOT_CARS(1,3,2)/I9C	= C1+C2+C3+C4+C5+C6+C7+C8; 		AS 		'CARS'				LABEL TOT_CARS		OVER
BAR																											OVER
-***********																									
-* LIGHTS
-**********
-* FFFSS
'FFFSS - MID SIZE PICKUP E CHASSI'			AS			'FFFSS Mid Size Pickup e Chassi'	LABEL L1		OVER
-* ZZXXXX
'ZZXXXX - MINI UTILITY'					AS			'ZZXXXX - Mini Utility'			LABEL L2		OVER
-*ASDAS
'ASDAS - MEDIUM CROSSOVER UTILITY'			AS			'ASDAS - Medium Crossover Utility'	LABEL L3		OVER
-*TRANSIT
'OTHERS - CARS - LARGE VAN'					OR
'OTHERS - CARS - MEDIUM TRADICIONAL UTILITY' OR
'OTHERS - CARS - MINI PICKUP E CHASSI'		AS			'TRANSIT'							LABEL L4		OVER
-* OTHER LIGHTS
'OTHERS - CARS - MID SIZE PICKUP E CHASSI'	OR												
'OTHERS - CARS - MINI BUS'					OR												
'OTHERS - CARS - OTHERS'					OR							
'OTHERS - CARS - RIGID SEMI - LIGHTS (3.3 TO 3.5T)' 	AS 		'Other Lights'				LABEL L5		OVER
BAR																											OVER																									
RECAP TEST/I9C = L1+L2+L3+L4+L5; 		AS 	'XXX'					LABEL XYZ			OVER	
BAR																											OVER
-* TRUCKS
'TRUCKS'									AS			'TRUCKS'							LABEL TRUCKS	
END

-EXIT
  

ERROR
0 ERROR AT OR NEAR LINE 635 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC091) THE FML LABEL IS ALREADY DEFINED: OTHERS - CARS - RIGID SEMI - LIGHTS (3.3 TO 3.5T)
BYPASSING TO END OF COMMAND



I cant perform TEST recap. If I comment that line the code works. Why?

This message has been edited. Last edited by: Ricardo Augusto,

I got it when I changed the label of TEST recap to test.

I was not able to create a specific RECAP to each column. So I changed my fex to use FML with recap, because I need the ability to use this?
RECAP FIELD(number_of_column,end_column,increment)/format = (FIELD1 + FIELD2)/ FIELD3;

I got it when using values from FOR command.
How do I use fields with NOPRINT at SUM?

Thanks

From wf81crlang.pdf page 1659

"
input1, inputn
Are the input arguments for the call to the function. They may include numeric constants,
alphanumeric literals, row and column references notation, E notation, or labels, or names
of other RECAP calculations.
Make sure that the values being passed to the function agree in number and type with the
arguments as coded in the function.

"

Richardo,

It looks like you have figured out your recap issue.

RECAP is part of FML - Financial Modeling Lanaguage. You can search on FML or RECAP within the Forum and find several links to help out further.

I will mark the subject [SOLVE]. We would like member to indicate on the subject line the status of the topic by using the Tag your topic with keywords

Thank you for participating in the Focal Point Forum.


Kindest regards,
Tamra Colangelo
Focal Point Moderator
Information Builders Inc.

* Summit 2016 – June 13-17 in Reno, Nevada  - http://www.informationbuilders.com/events/summit

Yes, I solved it using SUBFOOT and RECAP.

 
...


ON SEGMENT RECAP
-* FORD SEGMENT SHARE
SUB_TOT1/D4.1% = (PRIOR_RENAVAM_FORD*100)/PRIOR_REN_SEGMENT_IND;
SUB_TOT2/D4.1% = 0;
SUB_TOT3/D4.1% = (QT_REN_FORD_DAY*100)/QT_REN_SEGMENT_IND_DAY;
SUB_TOT4/D4.1 = 0;
SUB_TOT5/D4.1% = (DST.RENAVAM_FORD*100)/DST.REN_SEGMENT_IND ;
SUB_TOT6/D4.1 = 0;
-*TOTAL INDUSTRY SEGMENTATION
SUB_TOT7/D4.1% = (PRIOR_REN_SEGMENT_IND*100)/PRIOR_TOTAL_IND;
SUB_TOT8/D4.1% = 0;
SUB_TOT9/D4.1% = (REN_SEGMENT_IND*100)/TOTAL_INDUSTRY;
SUB_TOT10/D4.1 = 0;
-*INDUSTRY SHARE
SUB_TOT11/D4.1% = (PRIOR_RENAVAM_FORD*100)/PRIOR_TOTAL_IND;
SUB_TOT12/D4.1% = 0;
SUB_TOT13/D4.1% = (QT_REN_FORD_DAY*100)/QT_REN_IND_DAY;
SUB_TOT14/D4.1 = 0;
SUB_TOT15/D4.1% = (DST.RENAVAM_FORD*100)/DST.TOTAL_INDUSTRY;
SUB_TOT16/D4.1 = 0;
-* FORD VOLUME
SUB_TOT17/I9C = DST.PRIOR_RENAVAM;
SUB_TOT18/I9C = 0;
SUB_TOT19/I9C = DST.RENAVAM;
SUB_TOT20/I9C = 0;


IF MODEL_REPORT NE ''
ON SEGMENT SUBFOOT
"<SEGMENT<SUB_TOT1<SUB_TOT2<SUB_TOT3<SUB_TOT4<SUB_TOT5<SUB_TOT6<SUB_TOT7<SUB_TOT8<SUB_TOT9<SUB_TOT10<SUB_TOT11<SUB_TOT12<SUB_TOT13<SUB_TOT14<SUB_TOT15<SUB_TOT16<SUB_TOT17<SUB_TOT18<SUB_TOT19<SUB_TOT20"
...

 

Thanks