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.

BI_Developer, you also say round but in your examples, your are truncating. For example, take 15,902,300. Is it 15,000,000 or 16,000,000? If this was me and something I needed ofter, I would create a DEFINE FUNCTION and name it ROUND(X,Y) where X is the number to round and Y is a power of 10. So to round to millions, Y would be 6, to round to 100 thousands, Y would be 5, etc.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, 2006

May be a pain to perform since you don't have any consistency : no rule seems to be applicable

Number 15 402 300 become 15 000 000
Number 154 033 become 150 000
Number 15 403.30 become 15 000

Sometime you're keeping the hundreds of thousands, sometime you don't Sometime you're keeping the thousands, sometime you don't The only rule is that you drop the hundreds

The below is giving exactly what you are requesting for the three sample numbers but may not work for other values You definitively have to define a rule to be able to perform a rounding pattern since no automatic conversion will exist to do that (as stated by jgelona, can be defined in a function)

-SET &N1 = 15402300;
-SET &N2 = 154033;
-SET &N3 = 15403.30;
-REPEAT NEWFMT FOR &I FROM 1 TO 3
-SET &DIV = IF &N&I.EVAL GT 1000000 THEN 1000000
- ELSE IF &N&I.EVAL GT 100000 THEN 10000
- ELSE IF &N&I.EVAL GT 10000 THEN 1000
- ELSE 1;
-SET &NA = &N&I.EVAL - MOD(&N&I.EVAL, &DIV);
-TYPE NEWFMT : &NA
-NEWFMT

@jgelona :

quote:

So to round to millions, Y would be 6, to round to 100 thousands, Y would be 5, etc.

According to BI_Developer, the 100 hundreds should be 4 since 154 033 become 154 000. So, the 10 thousands are not anymore 4 but 3 (15 403 become 15 000), etc...

Not a "normal" rounding patternThis message has been edited. Last edited by: MartinY,

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Thanks for noticing. I failed to notice that starting with WF8206, the doc has FPRINT listed as a simplified conversion function and a conversion function and I just used the simplified one. So your suggestion is best, will work for everyone.

The 20,805 number is being rounded to 1 significant digit. Therefore I think that rounding 20,805 --> 20,000 is correct. Wouldn't you agree?This message has been edited. Last edited by: dbeagan,

WebFOCUS 8.2.06

Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010

It looks like you just want to take the first two digits of whatever number and then replace the rest with zeros.

You can do it all in a compute. Below I have broken it out into three different COMPUTEs with the last COMPUTE showing how it can be all in one COMPUTE:

TABLE FILE ggsales
SUM DOLLARS/I11C
-*Get the number of digits in the number
COMPUTE LENGTH/I11=CHAR_LENGTH(TRIM_(LEADING,'0',DIGITS( DOLLARS, 10)));
-*Get the divisor needed to move the decimal point to left after the second digit
COMPUTE DIVISOR/I11C=POWER(10, LENGTH -2);
-*Take the FLOOR of the number divided by the divisor, then multiply the FLOORed number back by the divisor.
COMPUTE ROUND_DOWN/I11C=FLOOR(DOLLARS / DIVISOR ) * DIVISOR ;
-*All in one COMPUTE
COMPUTE allInOne/I11C=FLOOR(DOLLARS / POWER(10, CHAR_LENGTH(TRIM_(LEADING,'0',DIGITS(DOLLARS, 10))) -2) ) * POWER(10, CHAR_LENGTH(TRIM_(LEADING,'0',DIGITS(DOLLARS, 10))) -2);
BY CATEGORY
BY PRODUCT
END

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

Hallway

Prod: 8202M1

Test: 8202M4

Repository:

OS:

Outputs:

Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015

Or, if you want to actually round the number and not truncate after the first two digits, you just need to add 0.5 after dividing the number by the divisor. In this example I built two different functions that you can either round or round down from an indicated number of digits from the left:

DEFINE FUNCTION LEFT_ROUND(Number/I11, Num_digits/I2)
LENGTH/I11=CHAR_LENGTH(TRIM_(LEADING,'0',DIGITS( Number, 10)));
DIVISOR/I11=POWER(10, LENGTH - Num_digits);
LEFT_ROUND/I11=FLOOR((Number / DIVISOR)+0.5) * DIVISOR;
END
DEFINE FUNCTION LEFT_ROUNDDOWN(Number/I11, Num_digits/I2)
LENGTH/I11=CHAR_LENGTH(TRIM_(LEADING,'0',DIGITS( Number, 10)));
DIVISOR/I11=POWER(10, LENGTH - Num_digits);
LEFT_ROUNDDOWN/I11=FLOOR(Number / DIVISOR) * DIVISOR;
END
TABLE FILE ggsales
SUM
COMPUTE MYDOLLARS/D12.2=DOLLARS/100; AS 'Dollars'
COMPUTE ROUND/D12.2=LEFT_ROUND(MYDOLLARS, 2); AS 'Round'
COMPUTE ROUNDDOWN/D12.2=LEFT_ROUNDDOWN(MYDOLLARS, 2); AS 'Round,Down'
BY CATEGORY
BY PRODUCT
END

Returns:

Category

Product

Dollars

Round

Round Down

Coffee

Capuccino

23,815.90

24,000.00

23,000.00

Espresso

39,062.43

39,000.00

39,000.00

Latte

109,436.22

110,000.00

100,000.00

Food

Biscotti

52,633.17

53,000.00

52,000.00

Croissant

77,499.02

77,000.00

77,000.00

Scone

42,161.14

42,000.00

42,000.00

Gifts

Coffee Grinder

23,375.67

23,000.00

23,000.00

Coffee Pot

24,495.85

24,000.00

24,000.00

Mug

45,225.21

45,000.00

45,000.00

Thermos

23,858.29

24,000.00

23,000.00

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

Hallway

Prod: 8202M1

Test: 8202M4

Repository:

OS:

Outputs:

Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015