 |
|||||
|
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
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. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Abbreviating currency figure
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Abbreviating currency figure Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED]Abbreviating currency figure | Login/Join |
| Member |
I have an FEX (App Studio report) that produces a single value... an integer that I want to show as currency. Right now, when I run the FEX, I get the result: $2,733,000 (which is the correct value). However, I'd like to see this value abbreviated. Is there some way that I can display the following: $2.73M ? I prefer to see the abbreviated value, so that it's easier to visualize in a portal. Thanks.This message has been edited. Last edited by: <Emily McAllister>, WebFocus APP Studio (WFAS) v 8.1.05 MS Windows Server - 32 bit | ||
|
| Master |
TABLE FILE CAR PRINT CAR COMPUTE DOLLARS_INTEGER/I9C = 2733000; COMPUTE DOLLARS_ROUNDED/D12.2M = DOLLARS_INTEGER / 1000000; COMPUTE DOLLARS_DISPLAYED/A17 = FPRINT(DOLLARS_ROUNDED, 'D12.2M', 'A16') || 'M'; IF RECORDLIMIT EQ 1 ON TABLE SET STYLEMODE FIXED END CAR DOLLARS_INTEGER DOLLARS_ROUNDED DOLLARS_DISPLAYED --- --------------- --------------- ----------------- JAGUAR 2,733,000 $2.73 $2.73M | |||
|
| Master |
That would be my preferred solution. Simple and elegant. But $2,700,000 would be: $2.70M And $2,000,000: $2.00M That would be fine for me, but I can just imagine the pointy-haired boss wanting it to show as: $2.7M and $2M, respectively. Here's some nasty code that does just that, but I wouldn't recommend it to anyone:
DEFINE FILE ibisamp/car
SALES_TIMES_1000/A20V=FTOA(CAR.BODY.SALES * 1000, '(D12.2)', SALES_TIMES_1000);
-* The next line adds an asterisk in front of the sales number, to be used
-* later in the JavaScript code below.
JAVASCRIPT_MARKER/A50V='*' || SALES_TIMES_1000;
END
TABLE FILE ibisamp/car
PRINT CAR.COMP.CAR AS 'Car'
CAR.CARREC.MODEL AS 'Model'
SALES_TIMES_1000 AS 'Sales'
JAVASCRIPT_MARKER AS 'Human readable'
BY CAR.COMP.CAR NOPRINT
BY CAR.CARREC.MODEL NOPRINT
ON TABLE HOLD AS OUTPUT FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN
-HTMLFORM BEGIN
<html>
<head>
<script language="javascript">
function replace() {
// Examine all td tags and look for dollar amounts to convert
// to a human-readable format.
var x = document.getElementsByTagName("td");
var i;
// If a td element begins with an asterisk, assume it is a dollar amount
// that needs to be converted to a human-readable format.
//
// Remove asterisk and any commas first, and then convert to a
// JavaScript floating point number before calling the "abbrNum" routine.
for (i = 0; i < x.length; i++) {
if (x[i].innerHTML.slice(1, 2) == "*")
x[i].innerHTML = '$' + abbrNum(parseFloat(x[i].innerHTML.slice(2).replace(/,/g, '')), 2);
}
}
// Function to make dollar amounts human-readable. Found on the Internet here:
// http://stackoverflow.com/quest...ndly-format-e-g-1-1k
function abbrNum(number, decPlaces) {
// 2 decimal places => 100, 3 => 1000, etc
decPlaces = Math.pow(10,decPlaces);
// Enumerate number abbreviations
var abbrev = [ "K", "M", "B", "T" ];
// Go through the array backwards, so we do the largest first
for (var i=abbrev.length-1; i>=0; i--) {
// Convert array index to "1000", "1000000", etc
var size = Math.pow(10,(i+1)*3);
// If the number is bigger or equal do the abbreviation
if(size <= number) {
// Here, we multiply by decPlaces, round, and then divide by decPlaces.
// This gives us nice rounding to a particular decimal place.
number = Math.round(number*decPlaces/size)/decPlaces;
// Handle special case where we round up to the next abbreviation
if((number == 1000) && (i < abbrev.length - 1)) {
number = 1;
i++;
}
// Add the letter for the abbreviation
number += abbrev[i];
// We are done... stop
break;
}
}
return number;
}
</script>
</head>
<body onload="replace()">
!IBI.FIL.OUTPUT;
</body>
</html>
-HTMLFORM END
-EXIT
App Studio WebFOCUS 8.1.05M Windows, All Outputs | |||
|
| Member |
Thanks David. The value I want to display is based on an aggregation (of a data field). My output line of code looks like this: SUM TSV.TSV.PROFIT/D4.1C!D AS '' What I want to do is FORMAT the output/display to show an abbreviated currency total for the value of TSV.TSV.PROFIT. Depending on the other filter vales, that value will change, but I want to display the abbreviated result, regardless of what the value is. Sometimes it will be in the Millions (M), at other times the value will be in the Billions (B) or Thousands (K). I want the display to dynamically change. Thx. WebFocus APP Studio (WFAS) v 8.1.05 MS Windows Server - 32 bit | |||
|
| Master |
@Squatch - Thanks for the link to the 'Reader Friendly' format Stack Overflow thread. Taking a quick look at the algorithm provided in JavaScript there, it seems like the same kind of reformatting might be possible in a WebFOCUS DEFINE FUNCTION. If it is possible, then it might meet the requirements in MRd's second post. In any event, 'Reader Friendly' format would seem to be a good NFR, if it isn't already available. | |||
|
| Master |
I was thinking the same thing, but it looks like quite the challenge. App Studio WebFOCUS 8.1.05M Windows, All Outputs | |||
|
| Expert |
Not really that much of a challenge other than ensuring that your calculations are correct. Try this, I know that it doesn't deal with the "pointy-haired boss" wants but it will give you an idea from which to start. DEFINE FUNCTION F_ABBRNUM(Number/F15.6, Decimals/I2)
NumRound/P15 = Number;
NumAbbr/A20 = 'K,M,B,T';
NumRLen/I11 = INT((ARGLEN(20, LJUST(20, FPRINT(NumRound, 'P20', 'A20'), 'A20'), 'I11') - 1) / 3);
NumPrec/I11 = 10 ** (NumRLen * 3);
NumOut/F15.6 = Number / NumPrec;
NumSuff/A1 = GETTOK(NumAbbr, 20, NumRLen, ',', 1, 'A1');
Format/A8 = 'F15.'||LJUST(2,FPRINT(Decimals, 'I2', 'A2'), 'A2');
F_ABBRNUM/A20 = RJUST(20,FPRINT(NumOut, Format, 'A19')||NumSuff, 'A20');
END
TABLE FILE GGSALES
SUM COMPUTE NUMBER7/F15.6 = DOLLARS * 1000;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER7, 1);
COMPUTE NUMBER6/F15.6 = DOLLARS * 100;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER6, 1);
COMPUTE NUMBER5/F15.6 = DOLLARS * 10;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER5, 1);
COMPUTE NUMBER4/F15.6 = DOLLARS;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER4, 1);
COMPUTE NUMBER3/F15.6 = DOLLARS / 10;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER3, 1);
COMPUTE NUMBER2/F15.6 = DOLLARS / 100;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER2, 1);
COMPUTE NUMBER1/F15.6 = DOLLARS / 1000;
COMPUTE DOLL_DISP/A20 = F_ABBRNUM(NUMBER1, 1);
BY REGION
ENDT
| |||||||||
|
| Master |
Wow, Tony, you sure made short work of that. Awesome! App Studio WebFOCUS 8.1.05M Windows, All Outputs | |||
|
| Expert |
Thanks Squatch, I like these little tea-break posers  I see it as a jigsaw puzzle, you've got various pieces to connect in the right order  T
| |||||||||
|
| Expert |
LOL...
| |||
|
| Master |
Awesome code share Tony! I've added three items: 1. No decimal prints when 'decimals requested' is 0. 2. Handling of 'special case' where we round up to next abbreviation. 3. Output prefaced with '$'. Hopefully, I didn't introduce an issue when adding these items. -* File HumanReadable.fex
DEFINE FUNCTION F_ABBRNUM(Number/F15.6, Decimals/I2)
Number/F19.2 = Number;
Decimals/I2 = Decimals;
NumRound/P15 = Number;
NumAbbr/A20 = 'K,M,B,T';
NumRLen/I11 = INT((ARGLEN(20, LJUST(20, FPRINT(NumRound, 'P20', 'A20'), 'A20'), 'I11') - 1) / 3);
NumPrec/P15 = INT(10 ** (NumRLen * 3));
NumOut/F19.2 = Number / NumPrec;
-*
-* Handle the special case where we round up to the next abbreviation.
-*
NumSpecial/P15 = NumOut;
NumPrint/F19.2 = IF NumSpecial EQ 1000 AND NumRLen LT 4 THEN 1 ELSE NumOut;
NumRLenAdj/I11 = IF NumSpecial EQ 1000 AND NumRLen LT 4 THEN NumRLen + 1 ELSE NumRLen;
-*
NumSuff/A1 = GETTOK(NumAbbr, 20, NumRLenAdj, ',', 1, 'A1');
Format/A8 = IF Decimals EQ 0 THEN 'F15' ELSE
'F19.'||LJUST(2,FPRINT(Decimals, 'I2', 'A2'), 'A2');
F_ABBRNUM/A21 = '$'||LJUST(20,FPRINT(NumPrint, Format, 'A19')||NumSuff, 'A20');
END
-*
TABLE FILE GGSALES
PRINT SEQ_NO
COMPUTE
NEW_DOLLAR/P19.2C = DECODE SEQ_NO (1 12
2 0
3 1234
4 34567
5 918395
6 2134124
7 47475782130
8 999950
9 999950
10 999950000
11 10000000000000
12 12.50); AS 'Dollars'
COMPUTE
DECIMALS/I2 = DECODE SEQ_NO (1 1
2 2
3 0
4 2
5 1
6 2
7 2
8 0
9 1
10 0
11 0
12 1); AS 'Required,Decimals'
COMPUTE
DOLLAR_REFORMATED/A21 = F_ABBRNUM(NEW_DOLLAR, DECIMALS); AS 'Dollar,Display'
-*
IF SEQ_NO LE 12
-*
ON TABLE SET STYLE *
INCLUDE = endeflt, $
TYPE=TITLE, JUSTIFY = CENTER, $
TYPE=DATA, COLUMN = DOLLAR_REFORMATED, JUSTIFY = RIGHT, COLOR='WHITE',BACKCOLOR=RGB(#33a02c), STYLE = 'BOLD', $
ENDSTYLE
END  The code looks like it now passes the test cases listed in the Stack Overflow article, provided by Squatch. (Thanks Squatch!) http://stackoverflow.com/quest...ndly-format-e-g-1-1k Hopefully, folks could test further.This message has been edited. Last edited by: David Briars, | |||
|
| Master |
Good stuff! The more I look at this, the more I think it should be a standard feature in IBI's software. App Studio WebFOCUS 8.1.05M Windows, All Outputs | |||
|
| Master |
Yep, it would make a good format type. I am guessing as folks are doing more and more 'KPI' type dashboard reports (v. columnar grid), this type of 'human readable' format might be more often requested. | |||
|
| Master |
Yes, I agree. This would not be a frivolous new feature request. App Studio WebFOCUS 8.1.05M Windows, All Outputs | |||
|
| Member |
Thanks for the feedback, everyone. The provided code helped me solve my problem. I can now abbreviate the currency figures, for posting on a dashboard. Nice work! WebFocus APP Studio (WFAS) v 8.1.05 MS Windows Server - 32 bit | |||
|
| Master |
Pssssst... MRd, can you edit your original post to say "[SOLVED] Abbreviating currency figure"? The gods will smile favorably on you if you do that. App Studio WebFOCUS 8.1.05M Windows, All Outputs | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Abbreviating currency figure
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Abbreviating currency figureCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |