Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Percent Calculations By Groups
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] Percent Calculations By Groups
 Login/Join
 
Gold member
posted
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';
HEADCOUNT/I5 = 1;
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
SUM   HEADCOUNT
      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 SET PAGE-NUM NOLEAD
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, $

&HIDEHEADCOUNT
ENDSTYLE
END


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


WebFocus 7.7.03
Win7, all output
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Have you explored using the RECOMPUTE or SUMMARIZE command instead of SUB-TOTAL?


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Dan,

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


WebFocus 7.7.03
Win7, all output
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 52 | Location: NJ,USA | Registered: May 26, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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';
 HEADCOUNT/I5 WITH SEATS = 1;
 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
 SUM HEADCOUNT
 ON TABLE HOLD AS HOLDX
END
-*
-RUN
-READFILE HOLDX
-*
TABLE FILE CAR
 SUM 
  HEADCOUNT &NOPRINT
  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 SET PAGE-NUM NOLEAD
 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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
 
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
 
Posts: 175 | Location: Pomona, NY | Registered: August 06, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Try WITHIN TABLE


WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
 
Posts: 175 | Location: Pomona, NY | Registered: August 06, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
Master
posted Hide Post
...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
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
ON &GROUPWITHIN SUB-TOTAL


Won't you need RECOMPUTE not SUB-TOTAL?


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 80 | Registered: January 26, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Percent Calculations By Groups

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.