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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Webfocus rounding and subtotaling

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Webfocus rounding and subtotaling
 Login/Join
 
DLR
Member
posted
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
 
Posts: 26 | Registered: February 25, 2005Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Have you tried using packed-decimal (P) formats instead of double-precision (D)?


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
jgelona
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
DLR
Member
posted Hide Post
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
 
Posts: 26 | Registered: February 25, 2005Report This Post
njsden
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
jimster06
Guru
posted Hide Post
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
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
jgelona
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
WFLurker
Gold member
posted Hide Post
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
 
Posts: 78 | Registered: October 24, 2006Report This Post
jgelona
Master
posted Hide Post
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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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     [CLOSED] Webfocus rounding and subtotaling

Copyright © 1996-2020 Information Builders