BEx Analyzer Decimals Questions

[tnecnivo]

Hi BI Gurus,

There's one question regarding Decimals.

Right now, we have sent the Key Figure in BW as 0.00 decimals.

and we have also set decimals to 0.00 @ Column level in BEx Query.

The query is displaying correctly as the following pic, however, if we clicked on the particular cell, it then will display more decimals places, rather than just 0.00.

For example: we clicked on the particular cell (e.g. node 518 - Banker Acceptance), It shows -7153.219999979 on the text area in Excel, rather than just 2 decimals.

We have already checked the InfoCube based on the GL account, its also 2 decimals.

May I know how the decimals setting on Key figure level, and also BEx level works?

And more importantly, is there any way to fix this so that when user click on the list, it shows only 2 decimals?

Thanks alot.

Vince

[lou_matura1 · Accepted solution]

If you're building this in Bex, set up a formula KF in the column section

My "new selection", for example is amount, which is made up of the KF amount, Fiscal Year and Fiscal Period.

My "new formula" KF is New Amount.  The formula is as follows:

TRUNC (amount*100)/100

TRUNC is found under the mathematical functions.

For the results:

Under Amount, I have -584689.380000038. 

Under the New Amount, I have -584689.38, which is the value that I want to have.

I hope this helps.

Lou

Message was edited by: Lou Matura As a sidebar, I prefer this methodology when it comes to calculating revenues when the price of something is carried to more than 2 decimal points.

[tnecnivo · Accepted solution]

Hi Lou,

Thanks for the suggestion, really appreciated it, I've tried your Formula, however, the result has abit of a problem, it doesn't increased even when the figure > 5.

For example:-

The original Amount is -7153.219999979

The figure we want is : -7,153.22 (which showing @ the BEx query)

With This formula:-

TRUNC ((amount*100))/100 = -7153.21

Its really close, but it doesn't increase even the number in the back is more than 5.

Thanks,

[lou_matura1 · Accepted solution]

Hi Vincent.

I've tried to replicate this several ways, but will show you for 2...  Creating a column of fixed amounts in Bex.  Logically, it should work for a variety of scenarios.

FixedAmt01= -7153.215

FixedAmt02= 7153.215

Output is as follows where I have tested the trunc formula. 

FixedAmt01	testFixAmt01	FixedAmt02	testFixAmt02	testif_AMT01	testif_AMT02
-7153.215	-7153.22	7153.215	7153.22	-7153.22	7153.22
-7153.215	-7153.22	7153.215	7153.22	-7153.22	7153.22
-7153.215	-7153.22	7153.215	7153.22	-7153.22	7153.22

The last 2 columns have applied boolean logic.

For an amount <=0, I have: (fixedamt<=0)*TRUNC(((fixedamt-0.005)*100))/100.  For an amount >0, I have: (fixedamt>0*TRUNC(((fixedamt+0.005)*100))/100.

I believe that if you use the following in one formula KF column, it should take care of both scenarios for values/amounts >0 and 0<.

(fixedamt<=0)*TRUNC(((fixedamt-0.005)*100))/100+(fixedamt>0*TRUNC(((fixedamt+0.005)*100))/100

Basically, I have this set up as:

IF fixedamt<=0

   Truncate ((fixedamt-0.005)*100)/100

ELSE

   Truncate ((fixedamt+0.005)*100)/100

I haven't been able to find anything in my live data to run a test of the syntax against.

Any amount that is 0, will still be 0 because of the <=0 operand.

Lou

[lou_matura1 · Accepted solution]

Vincent.

I used the logical formula

((fixedamt<=0)*TRUNC(((fixedamt-0.005)*100))/100)+((fixedamt>0)*TRUNC(((fixedamt+0.005)*100))/100) and reversed the signs in the column of data and the formula did handle the value correctly and provided the correct answer.  Try it on your live data and let me know if this worked for you.

Thanks

Lou

Message was edited by: Lou Matura Just verified the number of Brackets...  Missed one in the original post.

[tnecnivo · Accepted solution]

Hi Lou,

Thanks a lot, the formula worked! but no reverse sign needed though.

but anyhow, thanks!

[former_member182470]

Hi,

Did you define any scaling factor in Query settings for your KF? If yes, remove it and put 0.00 decimals only.

Regards,

Suman

[tnecnivo]

Hi Suman,

No, we didn't define any scaling factor, only decimals.

Kind regards

[former_member182470]

Hi,

For example: we clicked on the particular cell (e.g. node 518 - Banker Acceptance), It shows -7153.219999979 on the text area in Excel, rather than just 2 decimals.

This behavior is normal only. When you click on a cell, it shows the original value in the Excel text area. To avoid this only, we put some settings in decimals. When a user looks a ta a workbook, he will be seeing 2 decimals only. 

7153.219999979 is the result of your KF calculation. We generally do not consider the whole result value in decision making. That's why, we use as many decimals as we required. You should explain to your user........

But if you publish your Query to Web Analyzer or Portal, the whole value will not be displayed at all. Only two decimals will be appeared.

Regards,

Suman

[tnecnivo]

Hi Suman,

Thanks for the advice, but just wondering, we have checked in DataSource and also InfoCube, all Key Figure comes with only 2 decimals, how the system actually generate so many decimals on the node grouping?

Thanks.

Kind regards,

[former_member182470]

Hi,

Does your KF is being arrived by Calculations......? Bex checks TCURX table during runtime of your Query. I suspect your calculation yields you the big value although your Cube and DS are having 2 decimals. What do you say.......? Is it impacting your Client's business.........?

Regards,

Suman

[tnecnivo]

Hi Suman,

The KF doesn't have any calculation, for the column:-

-      Selection with 0COMP_CODE

-      CKF (this Calculated Key figure is basic key figure with (NO DIM) function.

The query is using 0GL_ACCOUNT Hierarchy to do grouping, but not matter how we add up, the 2 decimals will always be 2 decimals.

For the TCURX table, I checked on it and it shows currency, anything particular I need to look for?

Customer mentioned that its hard for them to do further calculation.

Could this be a Bug? because there really isn't anything else to cause so many decimals when cube and datasource is 2 decimal, right?

Many thanks,

[former_member182470]

HI,

Why does your client do further calculations by based on the Cell value  which shows in text area? He can consider the displayed value. Isn't it......?

In TCURX table, decimals will be defined for your currency.

Regards,

Suman

[tnecnivo]

Hi Suman,

Basically what happened is that this query is used for WebI report, and then in WebI, it has a function called: " Export to Excel".

After exported, the excel re-act the same way as Bex.

User just said they might want to copied the from the cell or do some calculation, but its unclear what they really want to do with it.

We have tried to copied and paste or even do calculation, it seems to be just 2 decimals. Guess the user just do not what it there

By the way, TCURX table, the decimal, is it fixed? Can we change it?

If we changed that, would it helps this issue?

or from your point of view, maybe is best to raise SAP OSS?

Thanks,

[former_member182470]

Hi,

TCURX decimal should be fixed. It is applicable globally. What is the decimal there in TCURX currently?

Try to understand your User requirement, if you are not clear, then u can raise OSS. What do you say?

Regards,

Suman

[tnecnivo]

Hi Suman,

We cant seem to find MYR @ TCURX table, it got other sort of Currency etc.

Is there are different naming?

We are not onSite, its between a middle person explaining to us.

We have try to copied and paste or even do calculation without problem, so we have revert back to the middle person, who will explain to the user.

But for the time being, we would still like to know how to fix the decimal displaying differently @ Excel Text area. SAP OSS has been raised.

Kind regards,