In a report I have a field which shows the difference ( in % ) between two values: Say : "Turnover_2009" and "Turnover_2010".
Formula = ( ( Turnover_2010 / Turnover_2009 ) - 1 ) * 100 Format is D7.2%
When the outcome is positieve it's shown as : x.xx% When the outcome is negatieve it's shown as : -x.xx%
But sometimes this is confusing for our end-users. ( say : Turnover_2009 = 1000 ; Turnover_2010 = 1800 ) ( yes, this happens.. )
Outcome shown is : 80.00% And now al of a sudden the users think it's an index figure... hence 80% means -20% !
My question: " I want to explicitly add a + sign. Is there a "by design" way to do this? i.e. +80% instead off 80%?
I think I can manage with some computes, but I wonder whether it's already available but I'm missing it.. ( an option in de format dialog "Always add sign" would be a nice feature request... )
Greets, DaveThis message has been edited. Last edited by: Kerry,
_____________________ WF: 8.0.0.9 > going 8.2.0.5
February 24, 2010, 05:38 AM
<JG>
Sorry Dave
only options are to either show the + as a seperate column or convert to text, concat and right justify
February 24, 2010, 08:08 AM
Ram Prasad E
Here is a working example using FTOA,
SET CENT-ZERO=ON
TABLE FILE CAR
SUM
PCT.SALES
COMPUTE SALES_TXT/A20=FTOA(PCT.SALES,'(D12.2%)',SALES_TXT);
COMPUTE SALES_PLUS/A21=IF PCT.SALES GT 0 THEN '+'|SALES_TXT ELSE '-'|SALES_TXT;
BY COUNTRY
BY CAR
END
-EXIT
Ram, a made a tiny adjustment to your sample code because a double minus sign is displayed when a value is negative (i.e. --20.00%) and a value of 0 is displayed as -0.00% which may or may not be desired.
SET CENT-ZERO=ON
TABLE FILE CAR
SUM
COMPUTE PCT_SALES/D20.2 = IF COUNTRY EQ 'JAPAN' THEN -1 * PCT.SALES ELSE PCT.SALES; NOPRINT
COMPUTE SALES_TXT/A20=FTOA(PCT_SALES,'(D12.2%)', SALES_TXT);
COMPUTE SALES_PLUS/A21=IF PCT_SALES GT 0 THEN '+' | SALES_TXT ELSE SALES_TXT;
BY COUNTRY
BY CAR
END
But since it's not a numeric field anymore I can not use any kind of Totals.
For now, thanks. Case closed.
_____________________ WF: 8.0.0.9 > going 8.2.0.5
February 25, 2010, 09:57 AM
njsden
quote:
But since it's not a numeric field anymore I can not use any kind of Totals
You're right about that. You cannot RECOMPUTE nor SUMMARIZE the percentage because it is now a text field but what you can do is using a RECAP to calculate the total variance and then a SUBFOOT to display it along with any other totals you need. I know, that's a little extra work and you won't get nice borders around your subfoot components but hey, at least the +/- signs are going to be there
If you really need plus signs in front of your percentages, here is a way to do it using JavaScript. This only works for HTML output. The technique is stolen from this post by Francis Mariani:
The COMPUTE is simply a means to produce a couple of negative numbers in the output. Since the percentage column contains only numerics during the WebFOCUS processing phase, you can still SUMMARIZE the column.
SET CENT-ZERO=ON
-*
DEFINE FILE CAR
SALES/D12.2 = SALES ;
END
-*
TABLE FILE CAR
SUM PCT.SALES NOPRINT
AND COMPUTE PCTSALES/D6.1% = IF (PCT.SALES LE 15) THEN (-1 * PCT.SALES) ELSE PCT.SALES ; AS 'Sales %'
BY COUNTRY AS 'Country'
BY CAR AS 'Car'
ON TABLE SUMMARIZE
ON TABLE HOLD FORMAT HTMTABLE
END
-RUN
-*
-HTMLFORM BEGIN
<html>
<head>
<script language = JavaScript>
function addPlus()
{
var td = document.getElementsByTagName('td'); /* search for 'td' HTML tags */
for (i=0; i<td.length; i++) /* for instances where a td tag is found... */
if (td[i].innerHTML.indexOf('%') >= 0 && /* if the HTML text after the td tag contains '%' */
td[i].innerHTML.indexOf('-') != 0 && /* but not '-', to exclude negative values */
td[i].innerHTML.indexOf('0.0') != 0 && /* and not '0.0', to exclude zero values */
td[i].innerHTML.indexOf('100') != 0 && /* and not '100', to exclude the total */
td[i].innerHTML.indexOf('Sales') != 0) /* and not 'Sales', to exclude the column title */
td[i].innerHTML = '+' + td[i].innerHTML ; /* then add '+' to the front of the HTML text */
}
</script>
</head>
<body onload='javascript:addPlus();'>
!IBI.FIL.HOLD;
</body>
</html>
-HTMLFORM END
This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
February 28, 2010, 04:59 AM
FrankDutch
Dave maybe you can add a column heading that says.... Delta.... Even the less smart user will understand it is not an index but the difference in percentage...
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 01, 2010, 04:57 AM
Dave
Thanks Frank...
Current column heading is "delta... " ( dutch: verschil... ). But they're used to always see a + or - .
We're now moving all BI to WebFOCUS. And 'OH !.. Panic' it's not exaclty the same, I don't understand it anymore
_____________________ WF: 8.0.0.9 > going 8.2.0.5
March 01, 2010, 05:16 AM
<JG>
quote:
Even the less smart user will understand it is not an index but the difference in percentage...
Do your users use Excel?
I have to guess that very few of them do because you never see a + in a numeric or percentage column in Excel.
The only way in Excel and most other spreadsheet programs to display a +ve sign is to use a custom format. That then dispalys a negative as -+nn.mm% meaning it does not work.
Send your users back to school.
March 02, 2010, 08:29 AM
Dave
Yes, back to school is the best solution... ( excel, pdf, paper, everyhting is used.. )
only bad feeling I have:
The old BI-tool we're moving from has this ability since ages.. ( custom formats ). It's kinda hard to explain to the big bosses how this new $++ BI-tool doesn't support it.
P.S. How can I close this topic?
_____________________ WF: 8.0.0.9 > going 8.2.0.5
March 02, 2010, 10:25 AM
Francis Mariani
To close a topic on this forum, click the icon of your original post. You can modify the title here.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 02, 2010, 12:00 PM
susannah
use color. blue for positive red for negative or use style bold for positive italic for negative or use font ...
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
March 02, 2010, 12:09 PM
Dan Satchell
susannah, Now that's called thinking outside the box! Great suggestions.
WebFOCUS 7.7.05
March 02, 2010, 12:13 PM
Tom Flynn
quote:
Formula = ( ( Turnover_2010 / Turnover_2009 ) - 1 ) * 100 Format is D7.2%
i agree, Dave LEAD='+' TRAIL='%' and my favorite... TRAIL='pp' (for second differences) are features in a very old econometric model building software that i used...it went away along with the economy... but it was bliss on a stick!
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
March 03, 2010, 03:04 AM
Dave
All,
Thanks for the many suggestions. Colors, brackets, style, extra column containing a + or a -.
These are all workarounds that we figured out ourselves also.
Thing is... 'they' are used to a certain format which I can not achieve with webfocus.
Colors is nice, but not after it's been printed with a black-white printer!
etc. Feature request: instead of : MYFIELD/D7.2% = ... I like to use : MYFIELD/+D7.2%/-D7.2% = ...
( first being the format for positieve, second for negative values )
in an extreme example: MYFIELD/Great D7.2% !/this is bad D7.2% bummer = ...
_____________________ WF: 8.0.0.9 > going 8.2.0.5
March 03, 2010, 04:03 AM
<JG>
unfortunately no % sign
SET CURRSYMB ='+' -RUN DEFINE FILE CAR NSEATS/D10.2= IF COUNTRY EQ 'ENGLAND' THEN SEATS *(-1) ELSE SEATS; AFMT/A8=IF NSEATS GT -.00001 THEN 'D10.2M' ELSE 'D10.2' END TABLE FILE CAR SUM NSEATS/AFMT BY MODEL END
March 03, 2010, 09:26 AM
susannah
quote:
SET CURRSYMB ='+'
oh man, that's just brilliant!
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
March 03, 2010, 10:02 AM
Francis Mariani
Good thinking!
Tiny bit of a problem with totals and subtotals: since the format of the subtotal field is taken from the previous row's field when using dynamic formatting - when the last row is negative the + currency symbol is not displayed.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server