APAC SD Team
Category
Report Builder
Product Version
7.4
Rounding Up Not Correct
Answered
Hi there,
I have recently read this article https://community.yellowfinbi.com/topic/no-rounding-option because I was having some rounding problems. I did as Conner had suggested and set the number of digits to 5 (I am using pricing data so I only need 2 decimal places) but when I go to sum the numbers it is rounding it. And this is happening to that entire column. (EX. Price + Shipping = 61.58 + 19.98 = 81.56, but Yellowfin is showing as 80.) It's not even rounding it up, its rounding down. Some of the calculations are even off by multiple dollars. Any reasons as to why this would be happening?
Regards,
Tiana
The same question
Hi Tiana,
Thanks for reaching out. Without having more information here, I can only speculate but I'd think it likely Price and/or Shipping have aggregations applied to them within the report? I believe what you should be doing here is:
SUM ('Price') + SUM ('Shipping')or replace with whatever aggregations you have applied to Price and Shipping, instead of just doing 'Price' + 'Shipping' (if you were to look at the underlying SQL, you'd see that the calculations are different). You have to specify aggregations within Calculated Fields, because it does not reference column formatting settings, which are applied on top of the data in Yellowfin.Please give this a try and let me know if this seems to be what you were looking for here.
Regards,
Mike
Hi Tiana,
Thanks for reaching out. Without having more information here, I can only speculate but I'd think it likely Price and/or Shipping have aggregations applied to them within the report? I believe what you should be doing here is:
SUM ('Price') + SUM ('Shipping')or replace with whatever aggregations you have applied to Price and Shipping, instead of just doing 'Price' + 'Shipping' (if you were to look at the underlying SQL, you'd see that the calculations are different). You have to specify aggregations within Calculated Fields, because it does not reference column formatting settings, which are applied on top of the data in Yellowfin.Please give this a try and let me know if this seems to be what you were looking for here.
Regards,
Mike
http://prntscr.com/kzwqda
http://prntscr.com/kzwqu4
Hi Mike,
This is what I am trying to do. I am trying to add all the prices and all the fees based on timestamp, store, and order ID so that instead of having 6 or more rows all labeled price or fee, there is only 2 per order id.
Regards,
Tiana
http://prntscr.com/kzwqda
http://prntscr.com/kzwqu4
Hi Mike,
This is what I am trying to do. I am trying to add all the prices and all the fees based on timestamp, store, and order ID so that instead of having 6 or more rows all labeled price or fee, there is only 2 per order id.
Regards,
Tiana
Hi Tiana,
Thanks for your reply. What you're after here should be able to be accomplished via Freehand SQL Calculated Field statements, the exact scripting of which though is not something Support can assist with, unfortunately, as this falls in the realm of consulting. Support is here primarily to offer some guidance and to investigate and troubleshoot potential defective behavior. Client data-specific report building questions are not something we handle. Your Account Manager Drew said he will be reaching out shortly to discuss.
Regards,
Mike
Hi Tiana,
Thanks for your reply. What you're after here should be able to be accomplished via Freehand SQL Calculated Field statements, the exact scripting of which though is not something Support can assist with, unfortunately, as this falls in the realm of consulting. Support is here primarily to offer some guidance and to investigate and troubleshoot potential defective behavior. Client data-specific report building questions are not something we handle. Your Account Manager Drew said he will be reaching out shortly to discuss.
Regards,
Mike
Hi Mike,
Thank you for reaching out and responding. Hopefully I can get everything put together.
Regards,
Tiana
Hi Mike,
Thank you for reaching out and responding. Hopefully I can get everything put together.
Regards,
Tiana
Hi Tiana,
Actually, thinking about this more. This may actually require a Custom Function that contains a Group By as well as an aggregation function. We have some documentation to get you started which you may find helpful. These processes are intended for developers to create, it should be noted.
Custom Functions Overview
Aggregation Vs. Group By in Custom Functions
A working example of Aggregating a Text Column... you may be able to take this information and alter it so that the column uses a Sum aggregation on your numeric data rather than concatenating different text fields.
Beyond these three examples, please search through our community for things like "custom functions" and "column aggregation", you may find additional helpful use cases.
Hopefully you can get this figured out! Please let me know if you have any further questions for now.
Regards,
Mike
Hi Tiana,
Actually, thinking about this more. This may actually require a Custom Function that contains a Group By as well as an aggregation function. We have some documentation to get you started which you may find helpful. These processes are intended for developers to create, it should be noted.
Custom Functions Overview
Aggregation Vs. Group By in Custom Functions
A working example of Aggregating a Text Column... you may be able to take this information and alter it so that the column uses a Sum aggregation on your numeric data rather than concatenating different text fields.
Beyond these three examples, please search through our community for things like "custom functions" and "column aggregation", you may find additional helpful use cases.
Hopefully you can get this figured out! Please let me know if you have any further questions for now.
Regards,
Mike
Hey Mike,
So that report I was working on is now not needed. But I am working on a similar report, but the same issue is happening. Even if I take out the order status and even the date and just strictly sum the amount, it still won't show the decimal places that are there. Switching the number precision doesn't do anything either except put ".00" at the end. I am trying to sum these metrics by store per day, and when I strictly just take every line of data and put it into a table, then sum at the report level, it works fine. But once I go to do all of the calculating in the transformation is where the problem lies. I have made transformations that have done these similar calculations before, and they worked just fine, so I am not sure as to why this transformation is not. Do I still need to go about the custom function?
Regards,
Tiana
Hey Mike,
So that report I was working on is now not needed. But I am working on a similar report, but the same issue is happening. Even if I take out the order status and even the date and just strictly sum the amount, it still won't show the decimal places that are there. Switching the number precision doesn't do anything either except put ".00" at the end. I am trying to sum these metrics by store per day, and when I strictly just take every line of data and put it into a table, then sum at the report level, it works fine. But once I go to do all of the calculating in the transformation is where the problem lies. I have made transformations that have done these similar calculations before, and they worked just fine, so I am not sure as to why this transformation is not. Do I still need to go about the custom function?
Regards,
Tiana
When messing around with the new transformation, I have found that when I put the order ID in the transformation and then sum, the decimal places are correct, but as soon as I take them out, that is when they start to mess up. Do you have any suggestions as to how I should go about aggregating these by day? I am trying to take a lot of informations from one table and push as little information as possible (only what is needed) into another table to then make my reports. So I have millions of rows that I am trying to just sum up for the past, roughly, two years. so only about 730 days (rows) of data.
When messing around with the new transformation, I have found that when I put the order ID in the transformation and then sum, the decimal places are correct, but as soon as I take them out, that is when they start to mess up. Do you have any suggestions as to how I should go about aggregating these by day? I am trying to take a lot of informations from one table and push as little information as possible (only what is needed) into another table to then make my reports. So I have millions of rows that I am trying to just sum up for the past, roughly, two years. so only about 730 days (rows) of data.
Hi Tiana,
I know you were offered an alternate solution for your use case here and that it is being looked into, however, I'd still like to attempt replicating the issue as described here so I can log it and get this potentially defective behavior resolved. As such, I'm attempting to do so and am having difficulty replicating.
I'm curious: for your number values, are they being recognized as Numeric fields by Yellowfin? I built a small .csv report for my Input stage with numeric values with two decimal places and it seems to be aggregating okay in the aggregation stage:
If you're using a csv file for the Input stage, can you provide that csv file so I can test against it? If you're not, can you tell me what source you're using for your Input stage? Any other settings you've applied would be helpful as well. In addition to confirming the value is being recognized as Numeric, can you inform me of any non-default settings applied to these sections, for instance:
Also, to confirm: is the Aggregation incorrect when applying to the Aggregate step or only when in Design stage attempting to Output to the database?
I look forward to your response.
Thanks,
Mike
Hi Tiana,
I know you were offered an alternate solution for your use case here and that it is being looked into, however, I'd still like to attempt replicating the issue as described here so I can log it and get this potentially defective behavior resolved. As such, I'm attempting to do so and am having difficulty replicating.
I'm curious: for your number values, are they being recognized as Numeric fields by Yellowfin? I built a small .csv report for my Input stage with numeric values with two decimal places and it seems to be aggregating okay in the aggregation stage:
If you're using a csv file for the Input stage, can you provide that csv file so I can test against it? If you're not, can you tell me what source you're using for your Input stage? Any other settings you've applied would be helpful as well. In addition to confirming the value is being recognized as Numeric, can you inform me of any non-default settings applied to these sections, for instance:
Also, to confirm: is the Aggregation incorrect when applying to the Aggregate step or only when in Design stage attempting to Output to the database?
I look forward to your response.
Thanks,
Mike
Hi Mike,
As far as I know, the numbers are being recognized as numeric. https://prnt.sc/l6ehu8
The aggregation step does not work correctly, once added or in the design step. https://prnt.sc/l6ekei
There are no other non-default settings being applied to these numeric columns.
As far as my source, I am pulling a table from one of our databases.
Regards,
Tiana
Hi Mike,
As far as I know, the numbers are being recognized as numeric. https://prnt.sc/l6ehu8
The aggregation step does not work correctly, once added or in the design step. https://prnt.sc/l6ekei
There are no other non-default settings being applied to these numeric columns.
As far as my source, I am pulling a table from one of our databases.
Regards,
Tiana
Hi Tiana,
Thanks for your reply. I tried replicating this by pulling a table from the database, but when I have the decimal places parameter set to '2', it does show the correct values and not just double zero's.
I think the only remaining factors to isolate here are: which RDBMS is your table found in? I'd like to test against whichever that is as there is a chance this is RDBMS-dependent. And 2, what Data Type is your numeric field at the db-level? numeric(18,0), nvarchar(MAX), smallint, etc.
Thanks,
Mike
Hi Tiana,
Thanks for your reply. I tried replicating this by pulling a table from the database, but when I have the decimal places parameter set to '2', it does show the correct values and not just double zero's.
I think the only remaining factors to isolate here are: which RDBMS is your table found in? I'd like to test against whichever that is as there is a chance this is RDBMS-dependent. And 2, what Data Type is your numeric field at the db-level? numeric(18,0), nvarchar(MAX), smallint, etc.
Thanks,
Mike
Hey Mike,
https://prnt.sc/l6hdpm Do you need more information than that? I am not quite sure what you need in terms of the connection info.
Regards,
Tiana
Hey Mike,
https://prnt.sc/l6hdpm Do you need more information than that? I am not quite sure what you need in terms of the connection info.
Regards,
Tiana
Hi Tiana,
For some reason this most recent screenshot is not loading:
Regardless, what I'm referring to is when you go to create a report, you choose your data source first and in this case, since you're building an ETL, you are choosing a table from a database, what database is that? MySQL, Microsoft SQL Server, etc.
Then in your RDBMS' GUI, for example, if using MS SQL Server, you can find the data type for the corresponding column. In my test numeric data values, you can see here that it's a numeric field:
In MySQL Workbench, you can click the little 'i' that appears when hovering over a table:
Heading into Columns, and looking at the Type:
If you don't have access to your db, please reach out to your DBA or whoever has access, to please confirm this information.
Thanks,
Mike
Hi Tiana,
For some reason this most recent screenshot is not loading:
Regardless, what I'm referring to is when you go to create a report, you choose your data source first and in this case, since you're building an ETL, you are choosing a table from a database, what database is that? MySQL, Microsoft SQL Server, etc.
Then in your RDBMS' GUI, for example, if using MS SQL Server, you can find the data type for the corresponding column. In my test numeric data values, you can see here that it's a numeric field:
In MySQL Workbench, you can click the little 'i' that appears when hovering over a table:
Heading into Columns, and looking at the Type:
If you don't have access to your db, please reach out to your DBA or whoever has access, to please confirm this information.
Thanks,
Mike
Hey Mike,
It is a MySQL database. As far as figuring out the type of field, I will have to get back to you on that.
Regards,
Tiana
Hey Mike,
It is a MySQL database. As far as figuring out the type of field, I will have to get back to you on that.
Regards,
Tiana
He said that it is decimal (15,2)
He said that it is decimal (15,2)
Hi Tiana,
Thanks for confirming. I created decimal values in a MySQL data source setting the data Type to decimal(15,2) and still cannot replicate this:
I'm putting this into collaboration to see if any of my colleagues have any ideas or recommendations here since all these different setups have worked for me thus far.
Regards,
Mike
Hi Tiana,
Thanks for confirming. I created decimal values in a MySQL data source setting the data Type to decimal(15,2) and still cannot replicate this:
I'm putting this into collaboration to see if any of my colleagues have any ideas or recommendations here since all these different setups have worked for me thus far.
Regards,
Mike
There aren't any settings that I would have been able to mess with that would be the cause of this problem are there?
There aren't any settings that I would have been able to mess with that would be the cause of this problem are there?
Hi Tiana,
The only setting I can think of is the Number Precision Decimal setting, but you indicated in an earlier reply that by that time it's already turned to, for example, 80, and if you set the Decimal places to 2, then it shows up as 80.00, which would of course still be incorrect. Perhaps there's some other settings worth checking that are not coming immediately to mind, so I'm seeing what some of my colleagues might think to check and/or provide some insight into what may be happening here. Sorry we haven't gotten this sorted yet! I'll keep you posted.
Regards,
Mike
Hi Tiana,
The only setting I can think of is the Number Precision Decimal setting, but you indicated in an earlier reply that by that time it's already turned to, for example, 80, and if you set the Decimal places to 2, then it shows up as 80.00, which would of course still be incorrect. Perhaps there's some other settings worth checking that are not coming immediately to mind, so I'm seeing what some of my colleagues might think to check and/or provide some insight into what may be happening here. Sorry we haven't gotten this sorted yet! I'll keep you posted.
Regards,
Mike
Hi Tiana,
I think I may have found something that would explain this issue - can you confirm which build and version of Yellowfin you are currently on? If unsure, you can check this info by appending /info.jsp to your login URL (ex: localhost:8080/info.jsp).
Thanks,
Mike
Hi Tiana,
I think I may have found something that would explain this issue - can you confirm which build and version of Yellowfin you are currently on? If unsure, you can check this info by appending /info.jsp to your login URL (ex: localhost:8080/info.jsp).
Thanks,
Mike
http://prntscr.com/l7o947
http://prntscr.com/l7o947
Hi Tiana,
Thanks for confirming. Turns out we have a logged defect for "Aggregate producing incorrect sum due to rounding" that was fixed in 7.4.6 and you're on an earlier build. I was focused on searching for and attempting to replicate the decimal aspect of this issue, but it appears the root cause of that behavior was due to a rounding issue, an issue that also does not occur in the latest build, which is what I was testing in. Sorry for the delay on this!
Please download the update installer to upgrade to the latest build of Yellowfin, which can be found here, and let me know if this feature is then working as expected for you.
Regards,
Mike
Hi Tiana,
Thanks for confirming. Turns out we have a logged defect for "Aggregate producing incorrect sum due to rounding" that was fixed in 7.4.6 and you're on an earlier build. I was focused on searching for and attempting to replicate the decimal aspect of this issue, but it appears the root cause of that behavior was due to a rounding issue, an issue that also does not occur in the latest build, which is what I was testing in. Sorry for the delay on this!
Please download the update installer to upgrade to the latest build of Yellowfin, which can be found here, and let me know if this feature is then working as expected for you.
Regards,
Mike
Hey Mike,
That sounds great. We will have that updated tomorrow and I will get back to you on how that is working. Thank you!
Regards,
Tiana
Hey Mike,
That sounds great. We will have that updated tomorrow and I will get back to you on how that is working. Thank you!
Regards,
Tiana
Hi Tiana,
Excellent. I look forward to your update.
Regards,
Mike
Hi Tiana,
Excellent. I look forward to your update.
Regards,
Mike
Hi Mike,
After a little delay, we finally got Yellowfin upgraded to the newest version and the aggregation seems to be working just fine now. Thank you for all of your help!
Regards,
Tiana
Hi Mike,
After a little delay, we finally got Yellowfin upgraded to the newest version and the aggregation seems to be working just fine now. Thank you for all of your help!
Regards,
Tiana
Hi Tiana,
Excellent. Glad to hear it! I'll go ahead and mark this as Answered then.
Please don't hesitate to reach out with any further questions or concerns.
Regards,
Mike
Hi Tiana,
Excellent. Glad to hear it! I'll go ahead and mark this as Answered then.
Please don't hesitate to reach out with any further questions or concerns.
Regards,
Mike
Replies have been locked on this page!