Focal Point

[SOLVED] ROUND a whole number

This topic can be found at:

http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1097006196

http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1097006196

September 05, 2019, 05:42 PM

[SOLVED] ROUND a whole number

Hello allIs 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.

WF 8.2.01 APP STUDIO

PDF,HTML,EXL2K,Active

September 05, 2019, 05:50 PM

Prod: | WebFOCUS 7.6.10/8.1.04 | Upgrade: | WebFOCUS 8.2.06 | OS: | Linux | Outputs: | HTML, PDF, Excel, PPT |

In Focus since 1984 | |||||||

Know The Code |

September 06, 2019, 08:22 AM

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

September 06, 2019, 08:23 AM

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 pattern

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

September 06, 2019, 11:45 AM

You can DEFINE a function with two inputs:

1. The number

2. How many significant digits

Here is an example:

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

Example output:

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

WF 8.2.06

Win10 / IE11

AHTML EXL2K PDF

September 06, 2019, 11:59 AM

anumber/A20V = LTRIM(FPRINT(number, 'P20'));

Should be

anumber/A20V = LTRIM(FPRINT(number, 'P20', 'A20V'));

20 805 (which is greater than 20 748) should give 21 000 same as 20 748 that is giving 21 000

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

September 06, 2019, 03:13 PM

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?

WF 8.2.06

Win10 / IE11

AHTML EXL2K PDF

September 06, 2019, 03:28 PM

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.

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

Hallway | ||

WF(Prod): | 8202M1 | |

WF(Test): | 8202M4 | |

OS/Platform: | Win 10 | |

Outputs: | All |

September 06, 2019, 05:02 PM

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 |

Hallway | ||

WF(Prod): | 8202M1 | |

WF(Test): | 8202M4 | |

OS/Platform: | Win 10 | |

Outputs: | All |