Focal Point
[CLOSED] Webfocus rounding and subtotaling

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

January 30, 2012, 03:52 PM
DLR
[CLOSED] Webfocus rounding and subtotaling
I am having a issue with rounding an amount per line on an invoice and the subtotal is rounding after totaling.

My defines are:
DEFINE FILE WAGES
GROSSAMT/D12.2 = NHRDIST_AMT;
AGENCYAMT/D12.2=
IF NHRDIST_POSN EQ '724006'
THEN
(GROSSAMT * 10) / 100
ELSE
(GROSSAMT * 25) / 100;
END

It rounds each individual AGENCYAMT line fine but does not round until after subtotaling and then the subtotal is off by a few cents.

Any suggestions.

This message has been edited. Last edited by: Kerry,


WF 8.1.04
January 30, 2012, 05:45 PM
Dan Satchell
Have you tried using packed-decimal (P) formats instead of double-precision (D)?


WebFOCUS 7.7.05
January 31, 2012, 08:59 AM
jgelona
This is one of those things that used to be taught in the FOCUS Internals Class.

D12.2 is just a print format. Internally, D formats store the 15 most significant digits. So if your computation results in 13456.748937254. What prints is 13456.74 but the whole thing is stored in memory. The whole number is used if further calcalation (like SUM, SUBTOTAL, etc.). The whole number is also stored if one saves it in a .foc.

Same goes for F formats, but they are limited to 8 significant digits.

I took the Internals class back in the late 80's and P formats did not exist. But from working with them, this is what I have observed. P formats can store something like 31 digits but it respects the number of decimals. So if your number has 2 decimals, all that is ever stored, kept or used is the number rounded to 2 decimals.

This message has been edited. Last edited by: jgelona,


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
January 31, 2012, 11:11 AM
DLR
I have tried to use the 'P'acked format and it works but, it seems to be rounded backwards. It does the following
The Packed format seems to be rounding wrong.

EX: 316.05 * .25 = 79.0125 It rounds up to 79.02

EX: 429.98 * .25 = 107.495 It rounds down to 107.49


The total adds up correctly but the rounding is the issue now.
I am not concerned with the differnece of a few pennies, but the user seems to be.


WF 8.1.04
January 31, 2012, 11:35 AM
njsden
Thanks for the info jgelona. Good facts to keep in mind.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
January 31, 2012, 12:54 PM
jimster06
Consider using the 'half adjust and truncate' technique with the packed format. Google 'half adjust' to see some good discussions.
Be wary of negative numbers such that the half adjustment must also be negative.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
February 01, 2012, 09:21 AM
jgelona
DLR, you've got something else going on. Did you change GROSSAMT to a P format? What is the format of NHRDIST_AMT? If these are a D format, then the value may not be 316.05. If it is 316.059 then you would get the result you are seeing. As I said a D format stores the 15 most signigicant digits. When I run this:
 DEFINE FILE MACGYVER
   VAL1/D12.2=316.05;
   VAL1A/P12.4=VAL1*.25;
   VAL1B/P12.2=VAL1*.25;
   VAL1C/P12.4=VAL1B;
   VAL2/D12.2=429.98;
   VAL2A/P12.4=VAL2*.25;
   VAL2B/P12.2=VAL2*.25;
   VAL2C/P12.4=VAL2B;
 END
 TABLE FILE MACGYVER
 PRINT VAL1 VAL1A VAL1B VAL1C VAL2 VAL2A VAL2B VAL2C
 WHERE COUNTER EQ 1
 END


I get this:
VAL1       VAL1A   VAL1B     VAL1C    VAL2      VAL2A    VAL2B     VAL2C
316.05   79.0125   79.01   79.0100  429.98   107.4950   107.50  107.5000

so the P format automatically rounds correctly and only keeps the number of decimals define in the format.

This message has been edited. Last edited by: jgelona,


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
February 01, 2012, 11:16 AM
WFLurker
I find the D(double precision) more accurate if you are doing calculation such as Averages,Spreads or Day Weight due to the fact it stores more then 2 decimals. The calculation are usually bang on to the Excel formula.


WF 8105M
- Portal, Dashboard
- Rcaster, Data Migrator
- Windows 2012 Client Server
- Dev/App Studio 8105
- Data: SQL, Oracle, Neteeza,MVS
February 02, 2012, 12:08 PM
jgelona
WFLurker, P format matches Excel even better as Excel will keep the 17 most signigicant positions. I have programs with P24.17 for just such cases. (This is with Excel 2003, haven't tested with Excel 2010).

There are 3 main differences between P and D formats. First is the number of significant numbers that can be stored. P format can store numbers much larger and much smaller than D format. The second difference is rounding. P format automatically round results to the number of decimal positions being stored, D formats truncate. The 3rd difference is that every value in a P format will be stored with the same number of decimal position, the number of decimal positions with D formats is variable. A D12.2 field, internally (in memory or on disk in a .foc file) may have 123.12, 123.45678, 1.1, 12345, .123457835, a P format with 2 decimal positions will have 123.12, 123.46, 1.10, 12345.00, .12.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.