Focal Point
[CLOSED] Force plus sign in field

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/729109873

February 24, 2010, 04:54 AM
Dave
[CLOSED] Force plus sign in field
Hi all,

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,
Dave

This 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



WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
February 24, 2010, 10:22 AM
njsden
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


- Neftali.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 25, 2010, 03:40 AM
Dave
mmmyes...
That does work... Smiler

But since it's not a numeric field anymore I can not use any kind of Totals. Frowner

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 Wink



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 26, 2010, 12:06 AM
Dan Satchell
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:

http://forums.informationbuild...=284102792#284102792

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 Music


_____________________
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. Wink

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. Good One


WebFOCUS 7.7.05
March 02, 2010, 12:13 PM
Tom Flynn
quote:
Formula = ( ( Turnover_2010 / Turnover_2009 ) - 1 ) * 100
Format is D7.2%


OR Brackets for negatives:

D7.2B%

then color them in RED as Susannah suggested...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
March 02, 2010, 12:36 PM
susannah
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 = ...

Nice Thread


_____________________
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. Red Face


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