How to limit the values of EXP(AVG(log(@calculation("LOS"))))

Lokesh Obhilineni-Oracle

I was trying to replicate this tableau calculation in OAS

How can I limit the scale of the values in the calculation EXP(AVG(LOG(LOS))) to prevent the error "exceeded the maximum allowed numeric size"

I was getting the following error:

The values generated during the execution of the Log function exceeded the maximum allowed numeric size, please change the function to ensure that values larger than 1.7976931348623158e+308 are not generated. (HY000)

Gianni Ceresa

Im just trying to replicate the calculation logic

And that's what you shouldn't do, because it's the best way to get a wrong result and a really poor OAS implementation (which has good chances to make users ask to go back to Tableau and trash OAS).

Is it possible that some of your values are 0 or NULL? Maybe the error is misleading because instead of a max boundary you are hitting the minimum. ln(x) when x approach 0 becomes -∞. It's possible that the system translate that to the largest positive number because you went on the other side of the minimum negative number.

What does your data looks like? What's the min and the max? Do the usual data analysis ("know your data") job, find out what your data looks like and you will fix your error because the natural logarithm is a common mathematical operation with a very well known behavior.

Gianni Ceresa

now It's giving me the same output everywhere.

That's what I said above: you are mixing pre-aggregation and post-aggregation operations.

You have a AVG in your formula: what else did you expect? The average of a single value is the value itself, AVG is an aggregation function, it is applied to all the records of your query and a single value is returned.

And here again is why I said you should not translate 1:1 Tableau formula but should instead work on requirements.

Defines the real functional meaning of that formula, then find how to build that in OAS.

Philippe Lions-Oracle

Can you share more on the context of your need, what are you trying to compute functionally ? Perhaps some of the answer may be in sourcing from a DB directly. It would help to have a bit more details.

Thanks

Gianni Ceresa

Well, if the LOG function can't return results larger than 1.7976931348623158e+308, then your input to LOG can't be larger than EXP(1.7976931348623158e+308).

But then again, you are translating some logic just replicating it? Or are you building things to match the requirements?

Because you are mixing up pre and post aggregation manipulation, and not sure if that's exactly what you really want to do. Always keep in mind OAS is not Tableau, you shouldn't replicate 1:1 formulas, you should work on the requirements and then fully ignore how things have been done in Tableau and just develop your requirements in OAS.

Lokesh Obhilineni-Oracle

Im just trying to replicate the calculation logic

I tried to round off the values, used case when to check if the value in "Exp(value)" is < something. But nothing worked for me

Lokesh Obhilineni-Oracle

The logic is to calculate the geometric mean of LOS field

Lokesh Obhilineni-Oracle

You are right, So I have tried to filter out the negative values and calculate the geometric mean but now It's giving me the same output everywhere.

EXP(AVG(
CASE WHEN @calculation("LOS") > 0 AND @calculation("LOS") IS NOT NULL THEN LOG(@calculation("LOS")) ELSE NULL END))

How can I adjust this do calculate for row by row level

Lokesh Obhilineni-Oracle

Thank you, like you said I don't need AVG. It makes sense