Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [HELP!] How to ensure that percentages add up to 100%

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[HELP!] How to ensure that percentages add up to 100%
 Login/Join
 
Expert
posted
I cannot get the percentages calculated by PCT to add up to 100.

Sample code:

SET PAGE=NOPAGE
DEFINE FILE CENTORD
CNT_ORDER/D9 = 1;
END
TABLE FILE CENTORD
SUM
PCT.CNT_ORDER/D6.2 AS ''
BY PROD_NUM AS PRODUCT
ACROSS QUARTER AS ''
ON TABLE PCHOLD FORMAT EXL2K
END

Result:

              Q1      Q2      Q3      Q4
 PRODUCT
 -----------------------------------------
 1004       5.52    3.59    3.03    2.11
 1006       9.79   10.98   11.06   11.28
 1008      13.08   14.91   15.61   16.09
 1010        .27     .39     .36     .35
 1012       4.92    3.37    3.10    2.05
 1016        .64     .33     .32     .37
 1018       2.92    1.88    1.21    1.48
 1020      13.08   14.91   15.61   16.09
 1022       8.92    8.19    7.65    7.83
 1024       8.56    7.95    7.49    7.68
 1026        .27     .39     .36     .35
 1028       2.59    1.94    1.87    1.96
 1030       6.63    6.56    6.72    6.81
 1032      10.45   11.74   11.83   12.15
 1034       9.39   10.60   11.42   11.32
 1036       2.97    2.26    2.34    2.09

Totals calculated outside of WF:
          100.00   99.99   99.98  100.01

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<JG>
posted
Unless I'm very much mistaken you never will unless the divisors add upto a hundred or a factor of 100.

Use D20.9 to see what values are actually being created
 
Report This Post
Expert
posted Hide Post
OH NO! I need these to add up to 100. I thought I'd calculate the percentage manually but the ACROSS sabotages things...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
Francis, excel must used the internal format to calculated the percentage, Can you do the same as JG said?
A	1.00	2.00	1.00	1.00		0.13	0.22	0.33	0.17
B	3.00	3.00	1.00	2.00		0.38	0.33	0.33	0.33
C	4.00	4.00	1.00	3.00		0.50	0.44	0.33	0.50
									
	8.00	9.00	3.00	6.00		1.00	1.00	1.00	1.00
  



Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Platinum Member
posted Hide Post
Rounding "errors" have been around as long as computerized spreadsheets. I can remember struggling with the same thing with LOTUS 1-2-3.

Your problem is with the statment:

Totals calculated outside of WF:

By specifyiny the "D6.2 " format, and you have a couple of lines that the last digit rounds down, adding up the numbers displayed will result in the "error" you are reporting. This is strictly a display issue, not a math issue.

add

ON TABLE COLUMN-TOTAL

to your report, and see what happens. FOCUS will do the summation on the numbers to many decimal places, and then display THAT result, which should almost always be 100.00


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Expert
posted Hide Post
I use a higher degree of precision for the computation and a lower degree in the final display... I'll conjure up a sample...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
The trouble is that the output of this report is validated by another department in the organization. Totals calculated by WebFOCUS are not acceptable - I have to get the report amounts to add up to 100! A higher degree of precision (D13.10) still doesn't add up to exactly 100%.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
I hate accountants! Dump them the raw data and go figure out themselves. Big Grin


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
Even worse, the eventual accountant for this is the govt!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Although I tend to agree with Hua, I suspect that this in not an acceptable option. SO, if I was in your position, I would offer an alternative report:

SET PAGE=NOPAGE
DEFINE FILE CENTORD
CNT_ORDER/D9 = 1;
END
TABLE FILE CENTORD
FOOTING
"Note: Percentages may not total exactly 100% because of rounding display"
SUM
CNT_ORDER AS 'Cnt'
PCT.CNT_ORDER/D6.2 AS '%'
BY PROD_NUM AS PRODUCT
ACROSS QUARTER AS ''
ON TABLE PCHOLD FORMAT EXL2K
END

Which sort of does the same thing.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Expert
posted Hide Post
Luckily, it is acceptable if I alter one of the rows to ensure the columns total to 100. To solve the problem, I compute the columns of the last row to be 100 minus the sum of all the other rows (everything except the last row's value) and put these in a SUBFOOT.

-SET &ECHO=ON;

SET HOLDFORMAT=ALPHA
SET ASNAMES=ON

SET PAGE=NOPAGE
-*SET STYLEMODE=FIXED
-*SET PANEL=200
-RUN

DEFINE FILE CENTORD
CNT_ORDER/D9 = 1;
END
-RUN

TABLE FILE CENTORD
SUM
PCT.CNT_ORDER/D6.2 AS 'PCT_CNT'
BY PROD_NUM
ACROSS QUARTER

ON TABLE HOLD AS H001
END
-RUN

?FF H001

TABLE FILE H001
PRINT
PCT_CNT1
PCT_CNT2
PCT_CNT3
PCT_CNT4

COMPUTE X1036_PC1/D6.2 = 100 - TOT.PCT_CNT1; NOPRINT
COMPUTE X1036_PC2/D6.2 = 100 - TOT.PCT_CNT2; NOPRINT
COMPUTE X1036_PC3/D6.2 = 100 - TOT.PCT_CNT3; NOPRINT
COMPUTE X1036_PC4/D6.2 = 100 - TOT.PCT_CNT4; NOPRINT

BY PROD_NUM AS PRODUCT

WHERE PROD_NUM NE '1036'

ON TABLE SUBFOOT
"1036<TOT.X1036_PC1<TOT.X1036_PC2<TOT.X1036_PC3<TOT.X1036_PC4"

ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=TABFOOTING, ITEM=1, JUSTIFY=LEFT, $
END
-RUN


Now to attempt this in the real world...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
This is not uniquely a WF issue: an eon and a half ago, when I was writing in PL/1 or FORTRAN, I had the same issue. I addressed it using packed decimal with a precision beyond the display, then half-adjusted and truncated to the display precision.
It is just how the computer handles numbers and does arithmetic.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
Guru
posted Hide Post
Yes, I wonder if you change the Dx to Px if you get the same degree of rounding errors.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Expert
posted Hide Post
How about this for a solution:
Use COMPUTE PCT1/D9.5 = PCT.CNT_ORDER ;
instead of PCT.CNT_ORDER/D6.2
AND feed that into an Excel template (or whatever) to format total row for PCT1 as two decimal places... Check it out...

Seen as 5 decimal places: 100.00000 99.99999 99.99999 100.00001
Seen as 2 decimal places: 100.00 100.00 100.00 100.00
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
The Px theory doesn't work. I tried it this morning right after I read Francis' post. Bummer.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
Unfortunately, in this case I cannot use an Excel template.

The format of the spreadsheet is very strict. The spreadsheet is read by other software.

Unfortunately, even with the solution I posted, using a SUBFOOT to calculate the last line, I still may have problems: In Excel, even though the data looks like numbers, they're being treated as text - bummer Frowner


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
I would suggest calculating the PCT yourself, and let the last item get the remainder.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
quote:
A higher degree of precision (D13.10) still doesn't add up to exactly 100%.

Are you mixing dollar store items with airplane sales? and in 2-decimal points in text format? Good luck, eh.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
A solution:

-SET &ECHO=ON;

APP HOLDMETA temp
APP HOLDDATA temp

SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY
SET ASNAMES=ON

SET PAGE=NOPAGE
-*SET STYLEMODE=FIXED
-*SET PANEL=200
-RUN

-*-- Step 1 - Calculate Percentage for all Products by Plant by Quarter
DEFINE FILE CENTORD
ORDER_COUNT/D9 = 1;
PRODUCT/A10 = IF PROD_NUM EQ '1036' THEN 'OTHER' ELSE PROD_NUM;
END
-RUN

TABLE FILE CENTORD
SUM
PCT.ORDER_COUNT/D6.2 WITHIN QUARTER AS 'PCT_CNT'
BY PLANT
BY QUARTER
BY PRODUCT

ON TABLE HOLD AS H001
END
-RUN

-*-- Step 2 - Calculate Percentage for "Other" Product by Plant by Quarter
-*--          The percentage of this Product will ensure that the totals
-*            by Plant by Quarter add up to 100
-*            This is necessary due to an idiosyncrasy regarding PCT.:
-*            The percentages do not total to 100
TABLE FILE H001
SUM
COMPUTE PRODUCT/A10 = 'OTHER';
COMPUTE PCT_CNT1/D6.2 = 100 - PCT_CNT; AS 'PCT_CNT'
BY PLANT
BY QUARTER

WHERE PRODUCT NE 'OTHER';

ON TABLE HOLD AS H002
END
-RUN

-*-- Step 3 - Create the report of Percentages for all Products by Plant by Quarter
-*            The original percentage for OTHER Product is excluded
-*            and the manually calculated percentage is included
TABLE FILE H001
SUM
PCT_CNT

BY PLANT NOPRINT
BY PRODUCT

ACROSS QUARTER AS ''

ON PLANT PAGE-BREAK SUBHEAD
"PLANT: <PLANT"

WHERE PRODUCT NE 'OTHER'

ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, ITEM=1, JUSTIFY=LEFT, $
ENDSTYLE

MORE
FILE H002
END
-RUN

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
If you try D20.16, you might get the results you want. But that many decimals displayed in the spreadsheet probably isn't acceptable.

SUM PCT.CNT_ORDER/D20.16


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
Another alternative, that seems to work, is to use the EXL2K FORMULA output format.

DEFINE FILE CENTORD
 CNT_ORDER/D9 = 1 ;
END
TABLE FILE CENTORD
 SUM PCT.CNT_ORDER/D6.2 AS ''
 BY PROD_NUM AS PRODUCT
 ACROSS QUARTER AS ''
 ON TABLE PCHOLD FORMAT EXL2K FORMULA
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
There are very strict rules regarding this particular Excel report - no FORMULA, no SUBTOTAL, columns must add up 100.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
Another option would be to use
SET PAGE=NOPAGE
DEFINE FILE CENTORD
CNT_ORDER/D9 = 1;
END
TABLE FILE CENTORD
SUM
PCT.CNT_ORDER/D6.2 AS ''
BY PROD_NUM AS PRODUCT
ACROSS QUARTER AS ''
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

You'll see the display in excel with 2 decimals, but as soon as you select a cell you'll see it's showing all decimals.

In Excel I get 100 for all your columns.

Frans


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Platinum Member
posted Hide Post
I've experienced the same problem -- row total %s being 99.9 or 100.1, luckily my reports arent for the gov't. so I resolved it by redefining the field and setting it to 100% if it was greater than 99.8%.

Another issue I've run into was if the forecast for a product no longer carried is 0, then I have to set my %s to 8.3%, but the last four months need to be 8.4% so when I compute the last four months I add .1%.

I don't use the pct. operator because the way my reports are laid out it doesn't work for me, I compute all my fields.

Hope this helps.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Master
posted Hide Post
Francis,

I have had to do this same thing several times and your final solution is the only way I have ever found that always works.

I used to work for an Independent Oil & Gas Exploration company. We had to produce monthly and quarterly partnership statements and quarterly royalty checks for 40 drilling partnerships and over 8000 partners in those partnerships. When you have partnership percentages down to 8 decimal positions but payments are only 2 decimal positions, rounding can cause all kinds of problems. If checks were off by a penny, calls were being made. One thing that made it a little easier was the perspectus said there was no rounding up, fractions of pennies were truncated and rolled into the gereral partner's share (that was us).

What I usually do is:
1. HOLD data in a FOCUS file
2. Update last row with values needed to insure everything adds up
3. Print Report


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
I looked into the FORMULA suggestion. Unfortunately not a great idea - while it's true that the columns total to 100, it's the individual two-decimal-place display values that need to total to 100, not the four-decimal-place internal cell values.

Thanks,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
I like jgelona's approach. Having a separate entry for the variance complies with most accounting practices(usually dictated by the accountants, in this case, your tax wasting "g"). No fixing data. Accountable.


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
<JG>
posted
Francis, give this a try
 
SET PAGE=NOPAGE
DEFINE FILE CENTORD
CNT_ORDER/P9 WITH PROD_NUM= 1;
END
TABLE FILE CENTORD
SUM CNT_ORDER
BY QUARTER
SUM CNT_ORDER
COMPUTE PCTCNT_ORDER/D9.2 =(CNT_ORDER/C1)*100;
BY QUARTER
BY PROD_NUM
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM PCTCNT_ORDER
BY PROD_NUM AS PRODUCT
ACROSS QUARTER 
ON TABLE SET ASNAMES ON
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-SET &LASTROW = &LINES;
TABLE FILE HOLD
SUM 
COMPUTE COLTOT1/D9.2= PCT1; NOPRINT
COMPUTE COLTOT2/D9.2= PCT2; NOPRINT 
COMPUTE COLTOT3/D9.2= PCT3; NOPRINT 
COMPUTE COLTOT4/D9.2= PCT4; NOPRINT  
SUM
COMPUTE MYROW/I9 = LAST MYROW + 1; NOPRINT
COMPUTE Q1/D9.2 = IF MYROW EQ &LASTROW THEN (100 - COLTOT1) + FST.PCT1  ELSE FST.PCT1;
COMPUTE Q2/D9.2 = IF MYROW EQ &LASTROW THEN (100 - COLTOT2) + FST.PCT2 ELSE FST.PCT2;
COMPUTE Q3/D9.2 = IF MYROW EQ &LASTROW THEN (100 - COLTOT3) + FST.PCT3 ELSE FST.PCT3;
COMPUTE Q4/D9.2 = IF MYROW EQ &LASTROW THEN (100 - COLTOT4) + FST.PCT4 ELSE FST.PCT4;
BY PRODUCT
ON TABLE PCHOLD FORMAT EXL2K
END 
 
Report This Post
Master
posted Hide Post
Pardon me if this is off base or the issue has been resolved, but I ran across this the other day in 'Creating Reports With WebFocus Language' (P. 2-25 in Print or 69 in the PDF):

Example: Calculating Column Percents
To calculate each employee share of education hours, issue the following request:
TABLE FILE EMPLOYEE
SUM ED_HRS PCT.ED_HRS BY LAST_NAME
ON TABLE COLUMN-TOTAL
END

The output is:
PCT
LAST_NAME ED_HRS ED_HRS
--------- ------ ------
BANNING .00 .00
BLACKWOOD 75.00 21.37
CROSS 45.00 12.82
GREENSPAN 25.00 7.12
IRVING 30.00 8.55
JONES 50.00 14.25
MCCOY .00 .00
MCKNIGHT 50.00 14.25
ROMANS 5.00 1.42
SMITH 46.00 13.11
STEVENS 25.00 7.12
TOTAL 351.00 100.00

Since PCT. and RPCT. take the same format as the field, the column may not always total 100%.

From the next example below (Row percents):
"If you require precise totals, redefine the field with a format that
declares decimal places (D, F)."


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [HELP!] How to ensure that percentages add up to 100%

Copyright © 1996-2020 Information Builders