Focal Point
[CASE-OPENED] Percent Calculations By Groups

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2007077926

March 26, 2013, 08:02 PM
sxschech
[CASE-OPENED] Percent Calculations By Groups
The counts seem to be adding up fine, looking to see how to get the percentages to be calculated properly. Also looking to see if the percentages could be shown two ways side by side. One version is to calculate the percent of the total for the sub group and the other is the percent of the overall group. The report will show various levels of detail, so percents would need to calculate within the groups accordingly. I can get the top level fine, one level down is fine, but then the overall percents don't calculate correctly. Maybe a reset on the total after each group is needed, but I don't know how to do that. Below is a code using CAR that I redefined to try and illustrate the issue. The actual report has many amper variables and is run from an html composer page where user selects the groupings and level of detail. Using the below example and considering this to be the actual report, the user could select to run the report by
1. TERM
2. TERM, COUNTRY
3. TERM, COUNTRY, CAR TYPE
4. TERM, COUNTRY, STARTCAR, CARTYPE, GRADCAR

Since the html page has the dropdown, I don't have a drop down in the fex and so have put in a default for the 4th, choice, so to see the results of the other choices, would need to comment/uncomment the code for &Groupings as appropriate.

Running the code as it is below (not changing which items are commented/uncommented. The overall total shows Headcount of 18 and YR4 of 5, the percentage should be 5/18 = 27.8%, but the report shows 12.5%

```-*GROUPING LEVELS
-*-DEFAULT &GROUPINGS = TERM
-*-DEFAULT &GROUPINGS = TERM RECOMPUTE BY COUNTRY
-*
-*-DEFAULT &GROUPINGS = TERM RECOMPUTE BY COUNTRY RECOMPUTE BY CARTYPE
-********************************************************************************************************
-***CAN THIS VERSION SHOW A CALCULATION OF YR4 TO HEADCOUNT AS WELL AS YR4 TO TOTAL COUNTRY?
-***		EXAMPLE:  TERM		COUNTRY		CARTYPE		HEADCOUNT	YR4		YR4PCT		YR4PCT of Country
-***				201330 		ITALY	  	ELECTRIC  	1  			1  		100.0%		25.0%
-***										PETROL		3			2		 66.7%		50.0%
-***				*TOTAL COUNTRY ITALY				4			3		 75.0%
-********************************************************************************************************

-DEFAULT &GROUPINGS = 'TERM RECOMPUTE BY COUNTRY RECOMPUTE BY STARTCAR RECOMPUTE BY CARTYPE RECOMPUTE BY GRADCAR'

-SET &HIDESTUFF  = IF &GROUPINGS CONTAINS 'GRAD' THEN '' ELSE 'FOC_NONE';
-SET &HIDEHEADCOUNT = IF &HIDESTUFF EQ '' THEN 'TYPE=DATA, COLUMN=HEADCOUNT, COLOR=WHITE, BACKCOLOR=( WHITE ), \$' ELSE ' ';
-SET &GROUPWITHIN = IF &GROUPINGS EQ 'TERM RECOMPUTE BY COUNTRY RECOMPUTE BY STARTCAR RECOMPUTE BY CARTYPE RECOMPUTE BY GRADCAR' THEN 'COUNTRY' ELSE 'FOC_NONE';

DEFINE FILE CAR
TERM/A6 = '201340';
CARTYPE/A10 = IF CAR EQ 'JAGUAR' OR CAR EQ 'PEUGEOT' OR CAR EQ 'ALFA ROMEO' OR CAR EQ 'DATSUN' THEN 'PETROL' ELSE 'ELECTRIC';
STARTCAR/A20 = 'SEDAN';
GRADCAR/A20 = IF BODYTYPE NE 'SEDAN' THEN 'OTHER' ELSE BODYTYPE;
YR4/I5 = IF SEATS LE 3 THEN 1 ELSE 0;
YR6/I5 = IF SEATS LE 4 THEN 1 ELSE 0;
YR7/I5 = IF SEATS LE 5 THEN 1 ELSE 0;
END
TABLE FILE CAR
HEADCOUNT WITHIN &GROUPWITHIN AS 'CountWithin' NOPRINT
YR4
COMPUTE YR4PCT/D5.1% = (YR4 / HEADCOUNT) * 100;
YR6
COMPUTE YR6PCT/D5.1% = (YR6 / HEADCOUNT) * 100;
YR7
COMPUTE YR7PCT/D5.1% = (YR7 / HEADCOUNT) * 100;
BY	  &GROUPINGS
-*TERM RECOMPUTE BY COUNTRY RECOMPUTE BY STARTCAR RECOMPUTE BY CARTYPE RECOMPUTE BY GRADCAR
ON  &GROUPWITHIN SUB-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
\$

TYPE=DATA, BACKCOLOR = WHITE,\$
TYPE=REPORT, GRID=OFF, \$

ENDSTYLE
END

```

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

WebFocus 7.7.03
Win7, all output
March 29, 2013, 03:17 PM
Kerry
Hi sxschech,

While we wait to see if other customers have a solution on this issue, please kindly keep us posted regarding the solution from the case. Thank you in advance for sharing with all.

Cheers,

Kerry

Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
March 31, 2013, 10:20 PM
Dan Satchell
You have SUMmed HEADCOUNT twice, and WF will use the last one for the subsequent percent calculations. If you remove the NOPRINT from the second COMPUTE for HEADCOUNT, you will see that the total is 40. 5/40 is 12.5%.

WebFOCUS 7.7.05
April 01, 2013, 12:03 PM
sxschech
Hi Dan,

I know that the HEADCOUNT is summing twice, I don't know how to tell it not to sum for the sub totals.

WebFocus 7.7.03
Win7, all output
April 01, 2013, 01:27 PM
Dan Satchell
Have you explored using the RECOMPUTE or SUMMARIZE command instead of SUB-TOTAL?

WebFOCUS 7.7.05
April 01, 2013, 02:16 PM
sxschech
Hi Dan,

Tried RECOMPUTE and SUMMARIZE, no change in result that I could see.

WebFocus 7.7.03
Win7, all output
April 01, 2013, 04:57 PM
wf1998
Need to use HEADCOUNT caculation in COMPUTE Statement

Env Prod:WebFOCUS 7702 ,Windows xp on 64, SQL Server 2008, IRF Tool
Env 1 Local: DevStudio 7702 - MS Windows XP SP2 - Apache Tomcat 5.0.28
Output: HTML, Excel and PDF
April 01, 2013, 05:10 PM
Dan Satchell
Here's an idea to consider: place the total for HEADCOUNT into a variable and use it where needed. It might be possible to do this with a multi-verb request too, but WF would make a complete mess out of your subtotal lines by placing the subtotals on the line after the subtotal text.

```-DEFAULT &GROUPINGS = 'TERM RECOMPUTE BY COUNTRY RECOMPUTE BY STARTCAR RECOMPUTE BY CARTYPE RECOMPUTE BY GRADCAR'
-*
-SET &NOPRINT = IF &GROUPINGS CONTAINS 'GRAD' THEN 'NOPRINT' ELSE '';
-*
DEFINE FILE CAR
TERM/A6 WITH SEATS = '201340';
CARTYPE/A10  = IF CAR EQ 'JAGUAR' OR CAR EQ 'PEUGEOT' OR CAR EQ 'ALFA ROMEO' OR CAR EQ 'DATSUN' THEN 'PETROL' ELSE 'ELECTRIC';
STARTCAR/A20 = 'SEDAN';
GRADCAR/A20  = IF BODYTYPE NE 'SEDAN' THEN 'OTHER' ELSE BODYTYPE;
YR4/I5       = IF SEATS LE 3 THEN 1 ELSE 0;
YR6/I5       = IF SEATS LE 4 THEN 1 ELSE 0;
YR7/I5       = IF SEATS LE 5 THEN 1 ELSE 0;
END
-*
TABLE FILE CAR
ON TABLE HOLD AS HOLDX
END
-*
-RUN
-*
TABLE FILE CAR
SUM
YR4
COMPUTE YR4PCT_G/D5.1% = (YR4 / HEADCOUNT) * 100;
COMPUTE YR4PCT_T/D5.1% = (YR4 / &HEADCOUNT) * 100;
YR6
COMPUTE YR6PCT/D5.1% = (YR6 / HEADCOUNT) * 100;
YR7
COMPUTE YR7PCT/D5.1% = (YR7 / HEADCOUNT) * 100;
BY &GROUPINGS
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,\$
TYPE=DATA, BACKCOLOR = WHITE,\$
TYPE=REPORT, GRID=OFF, \$
ENDSTYLE
END
```

WebFOCUS 7.7.05
April 01, 2013, 05:13 PM
sxschech
not sure where I am supposed to do the HEADCOUNT calculation with COMPUTE. I tried putting a Compute in from of HEADCOUNT WITHIN &GROUPWITHIN AS 'CountWithin'; that didn't change anything. I tried in the ON statement and got an error message The 'ON' Phrase is invalid.

WebFocus 7.7.03
Win7, all output
April 01, 2013, 05:57 PM
sxschech
Hi Dan,

I guess I'm not quite understanding how to do the variable. Your code as is gives the calculation off the overall total rather than the group total.

WebFocus 7.7.03
Win7, all output
April 11, 2013, 11:35 AM
Alex
```
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
PCT.CAR.BODY.DEALER_COST WITHIN CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.BODY.BODYTYPE
END
```

WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
April 11, 2013, 12:13 PM
sxschech
Thanks for the suggestion Alex. The issue we are having isn't within the group, but the total percentage for the group.

WebFocus 7.7.03
Win7, all output
April 11, 2013, 08:26 PM
Alex
Try WITHIN TABLE

WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
April 12, 2013, 05:41 AM
Alan B
Not going into the whole, just the issue described with a percentage not showing as required.
The issue is HEADCOUNT, as there are 2 of them.

The calculation for YR4PCT is based on YR4/HEADCOUNT. However the HEADCOUNT used is the 2nd one, not the first one as you want. This would be expected behaviour as the COMPUTE will use the latest value of HEADCOUNT, 40 not 18, giving the 12.5%.

If you use column notation to differentiate between the 2 HEADCOUNT fields, the result will be correct, e.g. (YR4 / C1).

Alan.
WF 7.705/8.007
April 15, 2013, 12:49 PM
sxschech
Hi Alan,

I tried your suggestion and while it gives the correct percentages overall, it no longer shows the correct within the groupings. How do I tell webfocus to use (YR4 / HEADCOUNT) for the groupings and then use (YR4 / C1) for the overall total? For example, For England with the new calculation, I'm getting 200%.

```*TOTAL CARTYPE PETROL	2	1	50.0%	1	50.0%	2	100.0%
*TOTAL STARTCAR SEDAN	4	2	50.0%	3	75.0%	4	100.0%
*TOTAL COUNTRY ENGLAND	4	2	200.0%	3	300.0%	4	400.0%
```

WebFocus 7.7.03
Win7, all output
April 16, 2013, 01:52 AM
Dave
...not really sure what you're trying to do.

But this might help.
In order to control what is going on I switch to 'Do it yourself mode':

Here a sample with all kind of different 'group percentages' and how it's done.

Hope it helps or inspires...

```SET ASNAMES = ON

DEFINE FILE CAR
GRANDTOTAL/A6 = 'TOTAL';
END

TABLE FILE CAR
SUM SALES AS GRANDTOTAL_SALES
BY	GRANDTOTAL

SUM SALES AS COUNTRY_SALES
BY	GRANDTOTAL
BY COUNTRY

SUM SALES AS CAR_SALES
BY	GRANDTOTAL
BY COUNTRY
BY CAR

SUM SALES
BY GRANDTOTAL
BY COUNTRY
BY CAR
BY SEATS

ON TABLE HOLD AS X
END

TABLE FILE X
SUM SALES
COMPUTE GROUP_PERC_1/D6.2% = 100 * COUNTRY_SALES / GRANDTOTAL_SALES; AS 'Country in total'
COMPUTE GROUP_PERC_2/D6.2% = 100 * CAR_SALES / COUNTRY_SALES; AS 'Car in country'
COMPUTE GROUP_PERC_3/D6.2% = 100 * SALES / CAR_SALES; AS 'Seats in car'
COMPUTE GROUP_PERC_4/D6.2% = 100 * SALES / COUNTRY_SALES; AS 'Seats in country'
COMPUTE GROUP_PERC_5/D6.2% = 100 * SALES / GRANDTOTAL_SALES; AS 'Seats in total'
BY	GRANDTOTAL
BY COUNTRY
BY CAR
BY SEATS
END
```

G'luck Dave

_____________________
WF: 8.0.0.9 > going 8.2.0.5
April 16, 2013, 04:04 AM
Alan B
quote:
ON &GROUPWITHIN SUB-TOTAL

Won't you need RECOMPUTE not SUB-TOTAL?

Alan.
WF 7.705/8.007
April 16, 2013, 02:54 PM
sxschech
Hi Dave,

Your solution looks interesting. Not sure how to fit it into my report as I'm trying to get totals for 3 different cols (YR4, YR6, YR7) whereas your code seems to be using only one col (Sales) and there are no overall totals for the groupings (England, France, etc.) It also looks like I would have to parse out all the ampervariables in order to get the individual BY lines for each type of SUM.

Hi Alan,

I tried the RECOMPUTE and didn't seem to make a difference in my actual report.

WebFocus 7.7.03
Win7, all output