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    WebFOCUS/FOCUS Forum on Focal Point     [HELP!] How to ensure that percentages add up to 100%

 Go Search Notify Tools
 [HELP!] How to ensure that percentages add up to 100%
Expert
 posted February 08, 2010 10:06 AM
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, 2005 IP
<JG>
 posted February 08, 2010 10:42 AM
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

 IP
Expert
 posted February 08, 2010 11:12 AM 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, 2005 IP
Guru
 posted February 08, 2010 12:26 PM 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, 2008 IP
Platinum Member
 posted February 08, 2010 12:35 PM 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.

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, 2005 IP
Expert
 posted February 08, 2010 12:38 PM 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, 2005 IP
Expert
 posted February 08, 2010 01:10 PM 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, 2005 IP
Guru
 posted February 08, 2010 01:17 PM Hide Post
I hate accountants! Dump them the raw data and go figure out themselves.

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

 Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008 IP
Expert
 posted February 08, 2010 01:19 PM 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, 2005 IP
Platinum Member
 posted February 08, 2010 01:30 PM 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, 2005 IP
Expert
 posted February 08, 2010 01:50 PM 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, 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, 2005 IP
Guru
 posted February 08, 2010 02:43 PM 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, 2003 IP
Guru
 posted February 08, 2010 02:51 PM 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, 2003 IP
Expert
 posted February 08, 2010 02:51 PM Hide Post
Use COMPUTE PCT1/D9.5 = PCT.CNT_ORDER ;
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, 2005 IP
Expert
 posted February 08, 2010 02:57 PM 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

 Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006 IP
Expert
 posted February 08, 2010 03:18 PM Hide Post
Unfortunately, in this case I cannot use an Excel template.

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

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, 2005 IP
Expert
 posted February 08, 2010 03:35 PM 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.04 Upgrade: WebFOCUS 8.2.07 OS: Linux Outputs: 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, 2006 IP
Guru
 posted February 08, 2010 04:13 PM 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, 2008 IP
Expert
 posted February 08, 2010 04:40 PM 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 ''

"PLANT: <PLANT"

WHERE PRODUCT NE 'OTHER'

ON TABLE SET STYLE *
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, 2005 IP
Virtuoso
 posted February 08, 2010 04:55 PM 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, 2007 IP
Virtuoso
 posted February 08, 2010 05:10 PM 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, 2007 IP
Expert
 posted February 08, 2010 10:44 PM 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, 2005 IP
Guru
 posted February 09, 2010 09:15 AM 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, 2007 IP
Platinum Member
 posted February 09, 2010 09:52 AM 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, 2007 IP
Master
 posted February 09, 2010 10:02 AM 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, 2006 IP
Expert
 posted February 09, 2010 04:18 PM 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, 2005 IP
Guru
 posted February 09, 2010 04:40 PM 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, 2008 IP
<JG>
 posted February 10, 2010 05:50 AM
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 ```

 IP
Master
 posted February 11, 2010 02:35 PM 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, 2010 IP