Is it possible to divide column totals?

Joshua Baron shared this question 11 months ago
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

/e9eee533f9b176be7f9e07860206bc70

Comments (5)

photo
1

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:

/4cfdad0687e27bf5b35547c41999d1dd

Which you can see is correctly around 10%:

/c31701289e4715d5b6e9d3d9646e97ae

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

photo
1

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).

photo
1

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:

/5a997eb932f6bf048068c4ebf47a2e41

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

photo
1

Hi Joshua,

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

Regards,

Mike

photo
1

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

photo
1

Mike,

I'm so sorry for not responding. I was out of the office for xmas when you sent those notes and I forgot all about responding when I returned. I hear what you are saying with respect to support vs. consulting - thank you for the clarification.

Sadly my issue is still not fixed and I don't think I am doing a good job of properly explaining it. I really don't care about the value at the row level, I only care about the column totals. So in my original screen grab (updated here) I would like to know if it's possible to take the sum of the entire column "objective met" and divide it by the sum of the entire column "# Emergencies" so in this case the end result would be 50% (1 / 2). If you would rather I approach consulting I can do that. Thanks and sorry once again for the delay. Have a good day!


/5e86305779c1a712575ce7554d6180dd

photo
1

Never mind, Mike - I figured it out. Thank you once again for your help!

photo
1

Hi Joshua,

Great! Thanks for letting me know. You are welcome.

Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

photo