Focal Point Banner


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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problem with EXL2K formula

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Problem with EXL2K formula
 Login/Join
 
Expert
posted
Shouldn't I be expecting the same result with and without FORMULA when saving a report in EXL2K format?

The following gives me two different results for the MARKUP_PCT COMPUTEd column - incorrect in EXL2K FORMULA format.

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
COMPUTE MARKUP/D10 = RETAIL_COST - DEALER_COST;
COMPUTE MARKUP_PCT/D6.2% = MARKUP/DEALER_COST * 100;
BY COUNTRY
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Francis, Did you get off by a factor ?
I ran this, and actually did 2 computes
MARKUP_PCT/D6.2% = MARKUP/DEALER_COST * 100;
MARKUP_PCT1/D6.2 = MARKUP/DEALER_COST * 100;
(Note, no % edit option).
 
Posts: 60 | Location: 2 penn | Registered: May 22, 2003Report This Post
Gold member
posted Hide Post
Sorry, the result was 1,953.68% vs 19.54 . Although when I click on the COLUMN, the formula seems correct, and in fact if I copy the formula into another cell, it is correct, so I'm not actually sure where the fault lies, but if you are getting a result like that, the workaround seems to be to take out the edit option % sign.
 
Posts: 60 | Location: 2 penn | Registered: May 22, 2003Report This Post
Expert
posted Hide Post
Francis,
<br />
<br />
If you were to save the "Excel"
output as an htm file and then open it up in wordpad or another text editor you will find the xml in all its glory.
<br />
<br />
The code from the first data row that will be created if you add Noreens extra column is
 
<tr height=17 style='height:12.75pt'>
<br />  <td height=17 class=xl24 width=105 style='height:12.75pt;width:79pt'>
ENGLAND</td>
<br />  
<td class=xl24 width=94 style='width:71pt'>JAGUAR</td>
<br />  
<td class=xl24 width=187 style='width:140pt'>V12XKE AUTO</td>
<br />  
<td class=xl26 width=75 style='width:56pt' x:num="7427">7,427
</td>
<br />  
<td class=xl26 width=71 style='width:53pt' x:num="8878">8,878</td>
<br />  
<td class=xl26 width=63 style='width:47pt' x:num="1451" x:fmla="=$E2    -$D2">
1,451</td>
<br />  
<td class=xl27 width=96 style='width:72pt' x:num="19.536825097616802"
<br />  x:fmla="=$F2    /$D2    *100">1,953.68%</td>
<br /> 
 <td class=xl28 width=103 style='width:77pt' x:num="19.536825097616802"
<br />  x:fmla="=$F2    /$D2    *100">19.54
</td>
<br /> 
</tr>

you will notice that although the formula is there and is correct for both instances, the displayed data is incorrect when the computed column is given the percentage sign edit.
The actual stored number
(because it is D format) is, correctly, the same in both instances.
This would explain why Noreen got the correct value when she copied the formula to another cell, as Excel would have recalculated the value and not just displayed what it had been supplied with.
<br />
<br />
In my opinion this is a fault with WebFOCUS because it is creating the original XML code (as seen in the text editor) and as such I would advise raising a case with IB.
<br />
<br />
Given the above evidence they shouldn't be able to say that it is not a bug(?) and be able to progress fairly quickly with the resoilution. I'm sure Noreen would agree?

This message has been edited. Last edited by: <Mabel>,



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
Actually, the only problem I see here is that you are adding the "* 100" to your percentage calculation. Since you are designating it as a percentage with the "/D6.2%", you do not need the "* 100". That is done for you, so your calculation comes out 100 times larger than it should.
 
Posts: 40 | Registered: March 10, 2004Report This Post
Expert
posted Hide Post
Terry, the % in the column format ONLY puts a percent sign at the end of the value, it does NOT do anything else.

The problem I'm having is obviously a bug, as Noreen and Tony pointed out.

I wonder why this has not been corrected already, it seems to fail on a very simple calculation.

I will open a case.

Thanks.

Francis.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Actually Francis, as Terry indicated, using the % does have an impact, but only in EXL2K FORMULA. So, you were both right. In EXCEL it does nothing.

It is not inconceivable that they skipped this little undocumented feature with % and EXL2K FORMULA.

Actually, just going into EXCEL without running a report and typing in the number 25 in a cell appears as 25. But, if you change the format of the cell to a percentage, you get 2500%.

Maybe this has something to do with the internal workings of EXCEL with the use of a percentage sign and the way that it works.

Ken
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
Expert
posted Hide Post
Francis, i agree with you..there's a lot wrong with formula than the feature you found. and i also agree with Terry...that this one has a work around...in focus the % is a formatting thing, but in excel, the act of converting a column to a % in order to get the % mark actually changes the cell value as well. so its a toss up...understanding the differences between the 2 products. Within the first hour of ever trying to use formula, we found that parentheses weren't respected in formula creation, and that on table column-total didn't work, it just recomputed. nasty when you have a price*quantity formula and you want it to total, and very def. not recompute. i reported both instantly. but don't know their progress, as we abandoned our 53 dev site. Would you be kind enough to keep us posted about what else you find with FORMULA on your 534 dev site? We asked for hidden columns as well, very important if we're going to make real use of this FORMULA thing.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
It is quite obvious that there are some flaws with EXL2K FORMULA. I too have experienced a few interesting things such as Susannah pointed out.

In my opinion, this is the nature of the beast when it comes to new product functionality. EXL2K FORMULA was pegged as one of the great new features and in retrospect, in trying to improve the product to keep up with growing demand of functionality, some things aren't completely functional right off the bat.

Mind you, that's no excuse.

Personally, I think the formulas included in the Excel worksheets could be much better. However, I find in my experience that the formulas are only there to provide a warm fuzzy feeling that the number displayed is actually what was intended.

Ken
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
Expert
posted Hide Post
Susannah, Ken, TerryW!

You have stated that using % at the end of a format of a DEFINEd or COMPUTEd field in EXL2K FORMULA does more than simply put a percent at the end of the value of the field!

I find this incredible! It behaves differently for EXL2K FORMULA and all the other formats?

How can we write an application where the user chooses the output format of the report?
The same fex is executed, but for the selected format - the same results should be expected.

The other problem I have with FORMULA is that the TOTALs of computed PCT fields do not always add up to 100%, but that's another story.

I will not be using EXL2K FORMULA.

Thanks for the information.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Problem with EXL2K formula

Copyright © 1996-2020 Information Builders