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.

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,

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

In FOCUS since 1986

WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2

WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

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 SKIP-LINE 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

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

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

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

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 SKIP-LINE 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