Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] ROUND a whole number
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] ROUND a whole number
 Login/Join
 
Platinum Member
posted
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.

This message has been edited. Last edited by: FP Mod Chuck,


WF 8.2.01 APP STUDIO
PDF,HTML,EXL2K,Active
 
Posts: 124 | Registered: July 21, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
Usually, divide + INT() + multiply


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
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: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 pattern

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


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
BI_Developer, in your examples the idea is to round to two significant digits?

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
 
Posts: 137 | Registered: October 19, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
@dbeagan

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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
@MartinY

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,


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 137 | Registered: October 19, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
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

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


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Guru
posted Hide Post

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
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 431 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] ROUND a whole number

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.