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 firstname.lastname@example.org and provide your corporate email address, company, and name.
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
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, 2003
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
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, 2004
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.
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, 2003
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.