Categories
I'm trying to use a formula that results in a percentage, but instead i'm getting a integer number
Hello Oracle Community,
This is the formula, that I'm using,
(Value A * Value B) / (Value C * Value D)
The selected column is the final result of that formula:
But, the result is wrong, because this should be right result:
(67 * 100) / (22 * 65) = 4.68%
So, the result is wrong, can you help me in this problem?
Best Regards,
Isaque Vereda
Answers
-
Hi @User_9SMBJ ,
I would replace 100 and 22 in your expression with 100.0 and 22.0. In this way you will execute a division between double values instead of integers (which can cause issues).
As an alternative, you could multiply both the numerator and the denominator by 1.0 .
0 -
When you calculation based on division
Assuming that A, B and C are integers, theirs sums will also be integers. In that case, OBIEE will perform an integer division. Assuming A, B and C are all positive, the denominator will always be larger than the nominator, which returns the zero result. You can explicitly casting the sums to doubles:
Example:
CAST(SUM(A by Category) AS DOUBLE)/CAST(SUM(A + B + C by Category) AS DOUBLE)
When dividing or multiplying with integers the result will also be an integer.
You need to cast at least one as double or you will always be doing an integer calculation. If both are already double then use "100.0" instead of "100" to keep the multiplication as a double multiplication
INT / INT = INT
Hope this explains..
Thanks,
Renuka
0 -
It keeps giving the wrong sum and it didn't work with any of the solutions presented.
0 -
Hi @User_9SMBJ ,
You can raise an SR under Oracle Analytics Cloud product, so that OAC support team would collaborate with you to debug this issue.
0 -
I would look at Manage Sessions and check the physical SQL generated. Use a simple report without other calcs going on, to make it easier to review the SQL. Check the aggregation - is it SUM? Is there anything else going on in the physical SQL which could be mapped to the model logic that might explain the unexpected results (aggregation rule, dimension hierarchy level of measures, etc.)?
I agree with Harriet - if you open an SR, a support engineer can look more deeply at your model and physical SQL.
0 -
Hi @User_9SMBJ ,
It may sound stupid, but it's worth checking.. Did you format the column to display decimal places? You can do it in the Criteria by selecting Column Properties first, and then Data Format. Since integer was the data type of your column, there is a chance that 0 has been selected for decimal places.
Of course, you still need to update your column expression first. This should work:
("Sales - CRM Resource System Usage"."Active Days Facts"."# of Logins" * 100.0) / (22.0 * "Resource Facts"."# of Users")1 -
Another suggestion would be try this as formula in DV by creating Calculated Item. I just tried a simple test case with similar formula and DV showed proper results.
0 -
+1 to Renuka and Federico. You're facing the typical INT division issue. I've quickly visualized it for you:
0 -
It keeps giving the wrong sum and it didn't work with any of the solutions presented.
0 -
Hi @User_9SMBJ ,
What do you mean exactly by wrong sum? I can't see any SUM in the expression you previously shared..
Are you referring to the Sum in the Aggregation Rule (Total Rows) field? If so, what are you getting and what is your expected output (a screenshot would help)?
0
