Is it possible to divide column totals?
Answered
I am looking for a way to divide the totals of two columns to display as a big number. In the screen grab below I have a small dataset that I want to display in a report. I need the total cases (Column 2) the total emergencies (Column 4) and I need to know the percentage of emergencies that were responded to in 6 minutes or less (column 6). So based on this small dataset I could say "In 2020 we had a total of 4 cases; 2 of which were emergencies and we responded to those emergences in under 6 minutes 50% of the time".
I have tried to do an advanced function on column 6 ("Division By Column") and also tried set analysis but neither gives me the value I am expecting. Any help would be greatly appreciated
The same question
Hi Joshua,
Thanks for reaching out. I recommend reaching out to your Account Manager to obtain Yellowfin University access. Specifically, the Data Analyst course may be of most use.
Generally, you should be able to divide the results of two columns by utilizing Calculated Fields. That said, specific Report Building solutions using business data is something that would be more in the realm of Consulting (possibilities which can also be discussed with your Account Manager), whereas Support deals more with identifying and troubleshooting product defects.
That said, I'm happy to provide pointers where able and it seems that in this case your question seems pretty straight forward, at least at first glance. Basically, if I'm understanding correctly, you are just wanting to divide a column by another.
Here's 2 metric columns and me building a new Calculated Field with a simple calculation:
Which you can see is correctly around 10%:
Of course it's technically 10.56, but the way SQL works with this particular datatype in our HSQL tutorial data, it strips away the decimal points. If you run the same calculation against a non-HSQL RDBMS, it will likely be a different data type that will allow you to see the decimal points there. As such, give this a try with Calculated Fields considering the information above and let me know how goes.
Hopefully this helps! Please let me know if you have any follow-up questions or concerns on any of this.
Regards,
Mike
Hi Joshua,
Thanks for reaching out. I recommend reaching out to your Account Manager to obtain Yellowfin University access. Specifically, the Data Analyst course may be of most use.
Generally, you should be able to divide the results of two columns by utilizing Calculated Fields. That said, specific Report Building solutions using business data is something that would be more in the realm of Consulting (possibilities which can also be discussed with your Account Manager), whereas Support deals more with identifying and troubleshooting product defects.
That said, I'm happy to provide pointers where able and it seems that in this case your question seems pretty straight forward, at least at first glance. Basically, if I'm understanding correctly, you are just wanting to divide a column by another.
Here's 2 metric columns and me building a new Calculated Field with a simple calculation:
Which you can see is correctly around 10%:
Of course it's technically 10.56, but the way SQL works with this particular datatype in our HSQL tutorial data, it strips away the decimal points. If you run the same calculation against a non-HSQL RDBMS, it will likely be a different data type that will allow you to see the decimal points there. As such, give this a try with Calculated Fields considering the information above and let me know how goes.
Hopefully this helps! Please let me know if you have any follow-up questions or concerns on any of this.
Regards,
Mike
Thanks, Mike. To be honest, I find it disappointing that functionality questions get elevated to the level of "consulting" where you have to pay for the privilege of using the tool. And I have taken the YFU data analyst course - albeit a year or so ago - and I don't remember this topic being covered there.
As for your example - I appreciate you providing something I can look at but it really doesn't help. Your example has only one row and the calculated field works as expected. My example has multiple rows and the formula you provided executes at the row level so throws errors when trying to divide by 0 (rows 2 - 4).
Thanks, Mike. To be honest, I find it disappointing that functionality questions get elevated to the level of "consulting" where you have to pay for the privilege of using the tool. And I have taken the YFU data analyst course - albeit a year or so ago - and I don't remember this topic being covered there.
As for your example - I appreciate you providing something I can look at but it really doesn't help. Your example has only one row and the calculated field works as expected. My example has multiple rows and the formula you provided executes at the row level so throws errors when trying to divide by 0 (rows 2 - 4).
Hi Joshua,
Thanks for your response. The reason these types of queries are generally handled by Consulting is that it takes knowledge of the report setup and often even the data itself to build reports to a desired specification. This requires spending some time looking in the users unique environment, understanding the business use case(s), looking at the data, and having the general know-how how to build reports to said desired specification, among other things. These are all things Consulting is simply better suited to handle. Support does try to help where we can, as I will continue to do in my next paragraph, but I think it important to identify where that line lies and set expectations as well.
As for your particular issue,The concept remains the same if you add a dimension in there, and rows whose values contain 0's:
This depends on your data though - specifically, the actual SQL statement that is being attempted. The RDBMS the query is being executed against can also be related here, as different RDBMS' and even different JDBC drivers may handle null's and 0's, and syntax in general, a bit differently.
Considering the above, what error specifically are you seeing when you attempt this? If what you're experiencing is a divide by zero error, this is generally a limitation of SQL. There are a couple relatively straight forward methods of mitigating this error though, such as writing case statements or NULLIF in a Freehand SQL statement. Here's an example of how to workaround this found on StackOverflow: How to avoid the “divide by zero” error in SQL?
Please take a look through this and try one of these solutions and let me know how goes. If still experiencing issues, please provide screenshot(s) of your Calculated Field calculation and the error you're seeing.
Regards,
Mike
Hi Joshua,
Thanks for your response. The reason these types of queries are generally handled by Consulting is that it takes knowledge of the report setup and often even the data itself to build reports to a desired specification. This requires spending some time looking in the users unique environment, understanding the business use case(s), looking at the data, and having the general know-how how to build reports to said desired specification, among other things. These are all things Consulting is simply better suited to handle. Support does try to help where we can, as I will continue to do in my next paragraph, but I think it important to identify where that line lies and set expectations as well.
As for your particular issue,The concept remains the same if you add a dimension in there, and rows whose values contain 0's:
This depends on your data though - specifically, the actual SQL statement that is being attempted. The RDBMS the query is being executed against can also be related here, as different RDBMS' and even different JDBC drivers may handle null's and 0's, and syntax in general, a bit differently.
Considering the above, what error specifically are you seeing when you attempt this? If what you're experiencing is a divide by zero error, this is generally a limitation of SQL. There are a couple relatively straight forward methods of mitigating this error though, such as writing case statements or NULLIF in a Freehand SQL statement. Here's an example of how to workaround this found on StackOverflow: How to avoid the “divide by zero” error in SQL?
Please take a look through this and try one of these solutions and let me know how goes. If still experiencing issues, please provide screenshot(s) of your Calculated Field calculation and the error you're seeing.
Regards,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Joshua,
I'm going to go ahead and mark this one as Answered 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
Hi Joshua,
I'm going to go ahead and mark this one as Answered 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
Replies have been locked on this page!