15,402,300 -> 15,000,000 15,403,30 -> 15,000,00 154,033 -> 150,000
Prod: | WebFOCUS 7.6.10/8.1.04 | Upgrade: | WebFOCUS 8.2.07 | OS: | Linux | Outputs: | HTML, PDF, Excel, PPT |
In Focus since 1984 | |||||||
Pity the lost knowledge of an old programmer! |
Number 15 402 300 become 15 000 000 Number 154 033 become 150 000 Number 15 403.30 become 15 000
-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
quote:
So to round to millions, Y would be 6, to round to 100 thousands, Y would be 5, etc.
DEFINE FUNCTION sigdig(number/P20, digits/I2) anumber/A20V = LTRIM(FPRINT(number, 'P20')); digit_cnt/I9 = CHAR_LENGTH(anumber); significant/A20 = SUBSTRING(anumber, 1, digits); roundup/I1 = IF SUBSTRING(anumber, digits+1, 1) GE '5' THEN 1 ELSE 0; sigdig/P20C = ( EDIT(significant) + roundup ) * 10 ** ( digit_cnt - digits ); END TABLE FILE ibisamp/ggsales PRINT SEQ_NO AS 'Significant,Digits' UNITS/I11C COMPUTE ROUND_UNITS/P20C = sigdig(UNITS, SEQ_NO); AS 'Rounded,Unit Sales' DOLLARS/I11C COMPUTE ROUND_DOLLARS/P20C = sigdig(DOLLARS, SEQ_NO); AS 'Rounded,Dollar Sales' WHERE SEQ_NO LE 4 ON TABLE SET PAGE NOLEAD END
Significant Rounded Rounded Digits Unit Sales Unit Sales Dollar Sales Dollar Sales ----------- ---------- ---------- ------------ ------------ 1 1,387 1,000 20,805 20,000 2 1,729 1,700 20,748 21,000 3 1,698 1,700 20,376 20,400 4 1,669 1,669 20,028 20,030
anumber/A20V = LTRIM(FPRINT(number, 'P20'));
anumber/A20V = LTRIM(FPRINT(number, 'P20', 'A20V'));
quote:Originally posted by BI_Developer:
Hello all
Is there a way to round numbers in the following way?15,402,300 -> 15,000,000 15,403,30 -> 15,000,00 154,033 -> 150,000
Please suggest.
Thank you.
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 ENDThis message has been edited. Last edited by: Hallway,
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 |