If there is, I for sure am not able to find one. The only me...

[Peter Vazny]

If there is, I for sure am not able to find one. The only mention of rounding is in this article: Rounding in Calculations and Conversions

There does not seem to be any explicit way to round numbers. Surely I am just missing something.

[David Beagan]

I guess the only built-in functions are just FLOOR and CEILING, for integer rounding.

For a more comprehensive function, you could define a function as in this Focal Point forum post:

http://forums.informationbuilders.com/eve/forumsa=tpc&f=7971057331&m=1097006196

[Martin Yergeau]

Using a different format as in the article, does round your number.

With proper format you should end up with the result you are looking for.

DEFINE FILE CAR

RETCST6 /D8.4 = RETAIL_COST / 6;

RETCSTR /D8.2 = RETCST6;

END

TABLE FILE CAR

SUM RETCST6

RETCSTR

BY COUNTRY

END

But if you are looking to round as per David post reference, the above is not the solution.

My sample is to round in a mathematical regular manner

[Peter Vazny]

@david.beagan I will look into that, but this is something I need to explain to our end users and unless it is possible in InfoAssist, it is a no go for us.

@MartinY I tried that before posting and it does not work on our system:

 

image.png148185 11.9 KB

 

the code behind that is:

DEFINE FILE deposits

YesterdayAccrued/D12.2=BEGACCRUEDINT ;

TodayAccrued/D12.2=DAILYINTAMT ;

Paid/D12.2=INTPAIDTODAY ;

Total/D12.2=ACCRUEDINT ;

END

 

TABLE FILE deposits

PRINT

BEGACCRUEDINT AS 'Accrued,Yesterday'

DAILYINTAMT AS 'Accrued,Today'

INTPAIDTODAY AS 'Paid'

ACCRUEDINT AS 'Total'

COMPUTE Adjust/D12.4=ACCRUEDINT - ( BEGACCRUEDINT + DAILYINTAMT - INTPAIDTODAY ) ; AS 'Straight Adjustments';

 

YesterdayAccrued

TodayAccrued

Paid

Total

COMPUTE DAdjust/D12.2=Total - ( YesterdayAccrued + TodayAccrued - Paid ) ; AS 'Define Adjustments';

 

COMPUTE CYesterdayAccrued/D12.2=BEGACCRUEDINT ;

COMPUTE CTodayAccrued/D12.2=DAILYINTAMT ;

COMPUTE CPaid/D12.2=INTPAIDTODAY ;

COMPUTE CTotal/D12.2=ACCRUEDINT ;

COMPUTE CAdjust/D12.2=CTotal - ( CYesterdayAccrued + CTodayAccrued - CPaid ) ; AS 'Compute Adjustments'

 

BY ACCTNUM

 

This is what I get:

.32541 - (.32185 + .00356 - .00) = .0000 <= correct

.33 - (.32 + .00 - .00) = .00 <= expected .01

.33 - (.32 + .00 - .00) = .00 <= expected .01

[David Beagan]

Peter, hopefully you can use P12.2 to solve the issue. It worked for me using the following test:

 

image.png93271 1.82 KB

 

SET PAGE=NOLEAD,STYLE=WARM

TABLE FILE GGSALES

PRINT SEQ_NO NOPRINT

COMPUTE Decimal/A1 = ' ';

COMPUTE Dnum1/D12.2 = .32541 ;

COMPUTE Dnum2/D12.2 = .32185 ;

COMPUTE Dnum3/D12.2 = .00356 ;

COMPUTE Dnum4/D12.2 = .00000 ;

COMPUTE Adjust/D12.2 = Dnum1 - ( Dnum2 + Dnum3 - Dnum4 );

COMPUTE Spacer/A9 = ''; AS '~~~'

COMPUTE Packed/A1 = ' ';

COMPUTE Pnum1/P12.2 = .32541 ;

COMPUTE Pnum2/P12.2 = .32185 ;

COMPUTE Pnum3/P12.2 = .00356 ;

COMPUTE Pnum4/P12.2 = .00000 ;

COMPUTE Adjust/P12.2 = Pnum1 - ( Pnum2 + Pnum3 - Pnum4 );

WHERE RECORDLIMIT IS 1

END

 

It seems that the D12.2 format, being a floating point internal representation, retains the additional precision beyond the two decimal places. P12.2 format does not.

[Peter Vazny]

@david.beagan - ok, that worked.

FYI for the future researcher: this does not necessarily get translated to SQL - it for sure does not for DB2. So if you use DEFINE instead of COMPUTE, you might get a different result depending on whether the statement got translated into SQL or not. And you arent really in control of that. It might get translated one time, but then you add some unrelated calculation somewhere else that breaks the SQL optimization and you will get a different number.

Sample:

DEFINE FILE deposits

YesterdayAccrued/P12.2=BEGACCRUEDINT ;

TodayAccrued/P12.2=DAILYINTAMT ;

Paid/P12.2=INTPAIDTODAY ;

Total/P12.2=ACCRUEDINT ;

Adjust/P12.2=Total - ( YesterdayAccrued + TodayAccrued - Paid ) ;

END

 

TABLE FILE deposits

 

PRINT

BEGACCRUEDINT AS 'Accrued,Yesterday'

DAILYINTAMT AS 'Accrued,Today'

INTPAIDTODAY AS 'Paid'

ACCRUEDINT AS 'Total'

COMPUTE Adjust/P12.4=ACCRUEDINT - ( BEGACCRUEDINT + DAILYINTAMT - INTPAIDTODAY ) ; AS 'Straight,Adjustments';

 

COMPUTE Spacer1/A3='~~~'; AS '~~~'

 

YesterdayAccrued AS 'Accrued,Yesterday'

TodayAccrued AS 'Accrued,Today'

Paid AS 'Paid'

Total AS 'Total'

Adjust AS 'Define,Adjustments'

 

COMPUTE Spacer2/A3='~~~'; AS '~~~'

 

 

COMPUTE CYesterdayAccrued/P12.2=BEGACCRUEDINT ; AS 'Accrued,Yesterday'

COMPUTE CTodayAccrued/P12.2=DAILYINTAMT ; AS 'Accrued,Today'

COMPUTE CPaid/P12.2=INTPAIDTODAY ; AS 'Paid'

COMPUTE CTotal/P12.2=ACCRUEDINT ; AS 'Total'

COMPUTE CAdjust/P12.2=CTotal - ( CYesterdayAccrued + CTodayAccrued - CPaid ) ; AS 'Compute,Adjustments'

 

BY ACCTNUM

WHERE ACCTNUM EQ 6487;

 

 

image.png123762 10.1 KB

 

Changed PRINT to SUM (although only a single record is expected in both cases):

 

image.png122857 9.98 KB

 

Just another day in the idiosyncratic WebFocus paradise

[Todd Wallace]

The INT function works in a compute.

TABLE FILE CAR

PRINT

COMPUTE rounding/D12.2 = INT( CAR.SPECS.WHEELBASE+.5 );

CAR.SPECS.WHEELBASE

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,

$

ENDSTYLE

END

[Martin Yergeau]

Good if you want to only keep integer part of a number, but if you want to keep some decimal, does not

[Drew DeBaecke]

You can extend this logic by multiplying by the appropriate power of 10, i.e. (CAR.SPECS.WHEELBASE+.5)100, before applying the applying the INT function, then dividing the result by the same power of 10:

COMPUTE rounding/D12.2 = INT( (CAR.SPECS.WHEELBASE+.5) 100 ) / 100;

[Todd Wallace]

Just saw on another post that you can use SQL passthrough to call a function from your database:

REVENUE_ROUNDED/I12CM = SQL.ROUND(REVENUE_US,0,0);

 

That is from MS SQL but your database surely has a round function built in that you can call.

This is the post:

https://myibicommunity.ibi.com/forum/t/sharing-direct-sql-function-calls/564

[Peter Vazny]

@todd.wallace this changes everything! Oh, the possibilities!