

Go  New  Search  Notify  Tools  Reply 
Silver Member 
Hi I've tried to search the forum on this subject but kept coming up short on a good solution. If someone can tell me where to find this or help with an example that would be great. I need to take a calculated field and round up to the next multiple of 10. This can be achieved in excel using the ceiling function  is there a similar function in WebFocus? An example of what I'm looking for is this: Number = 123 Should round up to 130  it does need to always round up to the next highest multiple of 10.This message has been edited. Last edited by: Kerry, WEBFOCUS 7.14 WEBFOCUS.8.04  

Expert 
Cathy, No dedicated function to my knowledge but you can always grow your own  DEFINE FUNCTION CEILING(VALUE/D20) CEILING/D20 = INT((VALUE / 10) + .95) * 10; END * Now use it TABLE FILE EMPDATA PRINT COMPUTE BASENUM/D9 = ABS(RDNORM('D9') * 100); COMPUTE NEWVAL/D9 = CEILING(BASENUM); BY FIRSTNAME NOPRINT ON TABLE SET PAGE NOLEAD ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * GRID=OFF, SIZE=11, FONT=ARIAL, $ ENDSTYLE END T
 

Silver Member 
Wow that's great. Thanks so much worked like a charm WEBFOCUS 7.14 WEBFOCUS.8.04  

Virtuoso 
Cathy  Can the underlying value ever be negative?  

Silver Member 
In this particular case no it would not. But would be interested if there is addtional coding to handle such an instance?? WEBFOCUS 7.14 WEBFOCUS.8.04  

Virtuoso 
DEFINE FUNCTION CEILING(Number/D12.2, Significance/D12.2) * * Mimics Excel's CEILING function: * CEILING(number,significance) * Returns number rounded up, away from zero, to the nearest multiple of * significance. For example, if you want to avoid using pennies in your * prices and your product is priced at $4.42, use the formula * =CEILING(4.42,0.05) to round prices up to the nearest nickel. * * The Excel function requires the two argument to be of like sign. This WebFocus function definition * relaxes that requirement: The arguments may be of same or opposite sign, with a result equal to the first * argument, rounded away from zero to an integral multiple of the second argument. * Multiple /D12 = INT( Number / Significance ) ; Adjust /D12.2 = ( Multiple * Significance NE Number ) * ( ABS (Number) / Number ); CEILING /D12.2 = IF (Significance EQ 0) THEN Number ELSE (Multiple + Adjust) * Significance ; END Buiding on Tony's sample code, to illustrate how it handles positive/zero/negative values: DEFAULT &SIGNIF= 10 TABLE FILE EMPDATA PRINT FIRSTNAME COMPUTE BASENUM/D20.4 = IF LAST FIRSTNAME EQ ' ' THEN 0 ELSE ABS(RDNORM('D9') * 100)  100; COMPUTE NEWVAL/D20.4 = CEILING(BASENUM,&SIGNIF); BY TOTAL HIGHEST NEWVAL SKIPLINE NOPRINT BY TOTAL HIGHEST BASENUM NOPRINT ON TABLE SET PAGE NOLEAD ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * GRID=OFF, SIZE=11, FONT=ARIAL, $ ENDSTYLE END  Jack Gross WF through 8.1.05  

Platinum Member 
Quoting an old one, I know... I'm not following why we want to divide and multiply by 10. This seems to do what I want: *CEILING(2.99) = 3 *CEILING(2.01) = 3 DEFINE FUNCTION CEILING(VALUE/D20) CEILING/D20 = INT(VALUE + 0.99); END WebFOCUS 8201, SP 0.1, Windows 7, HTML  

Virtuoso 
Shingles There are so many ways to accomplish the same result in WebFOCUS. If this works for you then go with it... Thank you for using Focal Point! Chuck Wolff  Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats  

Platinum Member 
The original request was to round up to the next 10. The results from your example should both be 10, not 3. Jack's function is elegant in that it allows you to set the significance. WebFOCUS 8.2.03  Production WebFOCUS 8.2.04  Sand Box Windows 2012 R2 Server HTML, PDF, Excel In FOCUS since 1980  

Virtuoso 
Eight years back  that must have been while at Chubb. I still tend to err on the side of elegance and generality.  

Platinum Member 
Hey Folks, So I was doing a little more work with Mimics code up there. It is not treating negative numbers correctly. So I went ahead and modified it a bit, and I also created a FLOOR function. Both of these spit out the same values that excel spits out. DEFINE FUNCTION CEILING(Number/D12.2, Significance/D12.2) * CEILING(81.46, 10) = 90 * CEILING(20.46, 10) = 20 Multiple /D12 = INT( Number / Significance ) ; CEILING /D12.2 = IF (Significance EQ 0) THEN Number ELSE IF ( Number ) LT 0 THEN Multiple * Significance ELSE IF ( Multiple * Significance EQ Number ) THEN Number ELSE (Multiple + 1) * Significance ; END DEFINE FUNCTION FLOOR(Number/D12.2, Significance/D12.2) * FLOOR(82, 10) = 80 * FLOOR(22, 10) = 30 Multiple /D12 = INT( Number / Significance ) ; FLOOR /D12.2 = IF (Significance EQ 0) THEN Number ELSE IF ( Multiple * Significance EQ Number ) THEN Number ELSE IF ( Number ) LT 0 THEN (Multiple  1)* Significance ELSE Multiple * Significance ; END DEFAULT &SIGNIF= 10 TABLE FILE EMPDATA PRINT FIRSTNAME COMPUTE BASENUM/D20.4 = IF LAST FIRSTNAME EQ ' ' THEN 0 ELSE ABS(RDNORM('D9') * 100)  100; COMPUTE CEILING_VAL/D20.4 = CEILING(BASENUM,&SIGNIF); COMPUTE FLOOR_VAL/D20.4 = FLOOR(BASENUM,&SIGNIF); BY TOTAL HIGHEST CEILING_VAL SKIPLINE NOPRINT BY TOTAL HIGHEST BASENUM NOPRINT ON TABLE SET PAGE NOLEAD ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * GRID=OFF, SIZE=11, FONT=ARIAL, $ ENDSTYLE END WebFOCUS 8201, SP 0.1, Windows 7, HTML  

Powered by Social Strata 
Please Wait. Your request is being processed... 