Unable to execute formula for member "xxx'. Value was either too large or too small for a Decimal.

[CAIGuySeanF]

Hello,

I'm attempting to leverage the UD8 Compound Annual Growth Rate (CAGR) business rule as a dynamic calculation.

The formula works great on most intersections, but I keep getting this error message on certain intersections:

--> Unable to execute formula for member 'xxx'. Value was either too large or too small for a Decimal. 

Does anyone know what is causing this our how I can overcome?  Even a single intersection error wrecks my cubeview presentation as you can see with the attached image.

I'm able to recreate the math in excel and the result doesn't seem unwieldy, so I'm not sure how to resolve. 

Thanks,

SeanUD8 CAGR Business RuleError Message being thrownMath - highlighting intersection with error via a QuickView

[JackLacava]

From documents, I believe Decimal values can exist between +79,228,162,514,264,337,593,543,950,335 and -79,228,162,514,264,337,593,543,950,335. If your number falls outside this range, at any point in the calculation, you'll get that error. You can probably break that range even if the number is technically inside the range but having a large amount of decimals.

[CAIGuySeanF]

I assumed it could be range driven, but in the example above, I don't ever come close to falling outside this range?

[Henning]

Hi, did you try converting your decimal to a string and logging this in the error log to see what might be the issue here?

[CAIGuySeanF]

excuse the ignorance on my part, but not sure how to do that?

[Henning]

Hi, no problem at all!

You will find the corresponding snippet when looking for "error":

You need to convert your final number to a string simply by adding a ".toString", e.g.:

Dim dDec As Decimal = 1
Dim sDec as string = dDec.ToString

Then you can log this as follows:

BRApi.ErrorLog.LogMessage(si, "My string value: " & sDec)

You then need to run the calculation (by opening your report) and then check the error log for the result. Likely you will see a number such as 0.000000000000000000000000000000000000000000000000000000000000000000000001 which will then tell you graphically (and as others said in their posts) why the system cannot handle this as the result likely falls out of the decimal range. 

Please note that you should not open your standard report, but create a test report that contains only a single cell! And this single cell should be the one returning your error. This is because otherwise you would write the result of each cell into the error log. That is not advisable as (1) it is a slow process and might take a while in particular for large reports as the snippet I showed you uses a BRApi and (2) you will otherwise see many numbers in the error log and you may not be able to locate the one causing the error easily. Also, do that in a test environment where only you are working on. If that log is accidentally triggered by many other users at the same time, performance will suffer and the log will be flooded.

Very important: Deactivate / delete the error log function after you used it! 

[CAIGuySeanF]

Thanks for the thorough explanation and detail.  I do see a long number, but can't seem to figure out how to round or convert it to a double.  I tried Daniel's idea below, but to no avail.  Any ideas? 

[JackLacava]

You want to round the CellAmount to something safer before you raise it to a power. 6 decimals should give you enough room, go lower if the error persists (assuming ExponCAGR is not crazy).

Math.Round(CAGRCalc.CellAmount, 6) ^ ExponCAGR - 1.0

 If this loss of precision is unacceptable, you can convert the Decimal to a Double by assigning CAGRCalc.CellAmount to a variable of that type, and perform the calculation with this new variable. However, double-based arithmetic is not exactly the same as decimal, so results could be surprising to non-programmers. Generally speaking, we typically recommend to stick to Decimal for financial data.

[CAIGuySeanF]

Hey Jack - this was the solution that Daniel mentioned below.  I tried this, and even today by rounding to 0.  Still no luck resolving the error. 

[CAIGuySeanF]

[CAIGuySeanF]

I tried multiple variations and double / rounding conversions (examples above), but no luck.  The error does change to my string value: NaN in the error log though.

[JackLacava]

The following approach is a last resort (string roundtrips should be avoided), but I believe in practice it should be fairly bombproof:

' get the original big number
Dim longNum As Decimal = yourCell.CellAmount  ' 0.98765432109876543210987654321
' allocate a variable that will contain the rounded Decimal
Dim x As Decimal
' round the Decimal, convert it to string, then reparse it as Decimal
Decimal.TryParse(Math.Round(longNum, 8).XFToStringForFormula, x)
' do the rest
Return x ^ (1/6) - 1

This effectively ensures that the rounded Decimal is not carrying around allocated zeros that still count, by recreating it completely.

For the record, this is what I'm seeing in my instance (7.2.2):

' this is a valid decimal - signed 28 digits
Dim dc = api.Data.GetDataCell("0.9876543210987654321098765432")
' this is not a valid decimal, would error out
' Dim dc = api.Data.GetDataCell("0.98765432109876543210987654321")
' this works fine
Return dc.CellAmount ^ 99 - 1

If these snippets don't work in your environment, there might be something dodgy in the installation that you might want to discuss with Support.

[DanielWillis]

Could you try storing the values that you're using in the formula as a double prior to the final calculation? I don't really know how it works behind the scenes but wondering if, due to the datatype of the source variables(s), it tries to implicitly store the value as a decimal prior to assigning it to the output variable/cell amount.

So,

dim x  as double = someDecimal
dim y as double = someDecimal
dim z as double = x^y

Just clutching at straws

[CAIGuySeanF]

It seems to be failing at the exponent part of this approach (Dim z).  What is odd, it looks like  negative value is causing an issue. Testing a Dim with an abs() renders.  I guess I could do "IF" logic to adjust, but that's odd to me that I couldn't leverage a negative value on an exponent double?

See examples below:

Dim Double

Result: No longer and error message, but noData being returned.

Making the base value ABS

Result Renders (obviously not the math I want, but the calc runs)

[CAIGuySeanF]

This seems to be working.. although, I'm still not exactly sure why I need to call it this way.

[DanielWillis]

I think like Jack hinted, it is the amount/size of numbers after the decimal place (i'm sure there is a better term for that.. precision?).

CellAmount accepts a decimal so falls over when you try to send the value there.

I wonder if you could (if its acceptable) store the value in a double and then round it and send it to CellAmount?

So

Dim DoubleCellAmount = CAGRCalc.Cellamount ^ ExponCAGR - 1

CAGRCalc.CellAmount = Math.Round(DoubleCellAmount,X)

where X is some value that is acceptable to you

[CAIGuySeanF]

Hey Daniel, 

Thanks for the suggestion.  Error message remains, but the values previously returning are being rounded as you would expect.

Sean