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,

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

OH NO! I need these to add up to 100. I thought I'd calculate the percentage manually but the ACROSS sabotages things...

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

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

I use a higher degree of precision for the computation and a lower degree in the final display... I'll conjure up a sample...

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%.

I hate accountants! Dump them the raw data and go figure out themselves.

Even worse, the eventual accountant for this is the govt!

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.

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...

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.

Yes, I wonder if you change the Dx to Px if you get the same degree of rounding errors.

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

The Px theory doesn't work. I tried it this morning right after I read Francis' post. Bummer.

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

I would suggest calculating the PCT yourself, and let the last item get the remainder.

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.

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,

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

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

There are very strict rules regarding this particular Excel report - no FORMULA, no SUBTOTAL, columns must add up 100.

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

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.

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

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,

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.

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 

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)."