Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to calculate specific total formula to specific columns? RECAP HELP
 Go New Search Notify Tools Reply
 [SOLVED] How to calculate specific total formula to specific columns? RECAP HELP
Guru
 posted November 16, 2015 01:51 PM
How do I calculate total using an specific formula to specific columns?

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

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Virtuoso
 posted November 16, 2015 02:23 PM Hide Post
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

WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

 Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013 IP
Guru
 posted November 17, 2015 04:49 AM Hide Post
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?

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Virtuoso
 posted November 17, 2015 08:19 AM Hide Post
Find (and read) the documentation on the RECAP command!
That will definitely help you to get you to where you want to be!

GamP

 - Using AS 8.2.01 on Windows 7 - IE11. in Focus since 1988

 Posts: 1960 | Location: Netherlands | Registered: September 25, 2007 IP
Guru
 posted November 17, 2015 12:13 PM Hide Post
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,

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Guru
 posted November 18, 2015 05:11 AM Hide Post
I got it when I changed the label of TEST recap to test.

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Guru
 posted November 18, 2015 06:17 AM Hide Post
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

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Guru
 posted November 18, 2015 06:31 AM Hide Post
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.

"

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Guru
 posted December 04, 2015 02:38 PM Hide Post
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

WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5

 Posts: 487 | Location: Toronto | Registered: June 23, 2009 IP
Guru
 posted December 07, 2015 05:00 AM Hide Post
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

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP