Rounding error in a calculated field

Mike Sheehan ●

Hi Siim,

Thanks for reaching out. I believe the pertinent question here would be are there any aggregations applied to the fields being referenced by your non-manual Calculated Field?

When using Calculated Fields Yellowfin looks at the underlying data without aggregations applied. For a quick example of how the results could vary, let's say each number corresponds with the following fields in the report itself:

5 = (Items)

22 = (Inventory)

660 = SUM(Sales)

So if you made a Simple Calculated Field as:

(Items) / (Inventory)/(Sales)

may have different results than

(Items) / (Inventory) / SUM(Sales)

depending on your underlying data. Best practice would be to match the aggregations set on top of your data at the report level in your calculated field as well.

Another possibility here is that the above doesn't apply to this case but perhaps there is some sort of other formatting applied to one or more of these fields and/or that if you looked at the underlying data itself and executed this statement (by running the SQL statement Yellowfin generates directly against the RDBMS), then you might see that "150.0000000000000000000000000000000000000000000000018" is your actual result.

That said, I'm unsure why it would be the case you'd be using so many decimal places. Any standard Decimal places, usually 2, would still display 150.00 here. Can you perhaps explain a little more on your use case here if this is required?

Thanks,

Mike

Hi Siim,

Thanks for reaching out. I believe the pertinent question here would be are there any aggregations applied to the fields being referenced by your non-manual Calculated Field?

When using Calculated Fields Yellowfin looks at the underlying data without aggregations applied. For a quick example of how the results could vary, let's say each number corresponds with the following fields in the report itself:

5 = (Items)

22 = (Inventory)

660 = SUM(Sales)

So if you made a Simple Calculated Field as:

(Items) / (Inventory)/(Sales)

may have different results than

(Items) / (Inventory) / SUM(Sales)

depending on your underlying data. Best practice would be to match the aggregations set on top of your data at the report level in your calculated field as well.

Another possibility here is that the above doesn't apply to this case but perhaps there is some sort of other formatting applied to one or more of these fields and/or that if you looked at the underlying data itself and executed this statement (by running the SQL statement Yellowfin generates directly against the RDBMS), then you might see that "150.0000000000000000000000000000000000000000000000018" is your actual result.

That said, I'm unsure why it would be the case you'd be using so many decimal places. Any standard Decimal places, usually 2, would still display 150.00 here. Can you perhaps explain a little more on your use case here if this is required?

Thanks,

Mike

Reply URL

1

Siim Neljandik ●

Hi,

Displaying this result is not where this causes problems. The problem appears when I use it in another field that in essence is comparing actual and budget. If it is 150- 5/22*660, then the result is -0.000000... and that causes the conditional formatting that is equal or greater than 0 not to trigger.

The current planned workaround is to change the number in the conditional format to -0.0001, however that will require changing multiple reports.

Thanks,

Siim

URL

1

Mike Sheehan ●

Hi Siim,

Ah, I see. The underlying potential issue mentioned here though may be the same based on the data at the db level. Have you had a chance to verify whether the result ending in ....0018 is the expected result based on querying directly against the RDBMS? Your mentioned workaround is also a pretty good workaround, especially if this is going to be an ongoing issue based on your data set. If this is defective behavior though, we should try and determine what's going on. At this point though, I suspect this has to do with the data itself, as that is what the calculated field calculations look at.

Regards,

Mike

URL

1

Mike Sheehan ●

Hi Siim,

I just wanted to check in and see how things are going with this.

Regards,

Mike

Reply URL

1

Mike Sheehan ●

Hi Siim,

I'm going to go ahead and mark this one as Resolved since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

Reply URL

Rounding error in a calculated field

Replies (3)