Discussions

Expand all | Collapse all

How to calculate a percentage on a Summary Report?

  • 1.  How to calculate a percentage on a Summary Report?

    Posted 05-01-2019 02:23
    I have a table to track work tickets.  I want to be able to run a monthly summary report which shows the percentage of work tickets completed by type.  Basically just [completed tickets]/[total tickets].  If I add a formula field into the table I can see an accurate percentage for each day, but if I run a monthly summary report the percentage is not accurate no matter how I tweak the different options.

    For example, lets say I have three types of tickets ... Landscape, Maintenance and Misc.  Everyday there will be tickets opened and closed for each type.  So for one day it might look like this:

    Type                    Opened Tickets     Closed Tickets
    Landscape          22                          8
    Maintenance       12                          6
    Misc                    15                          0

    Everyday will have different totals.  At the end of the month I want a summary report which calculates the percentage of the three ticket types.  What I want is very simple although it probably seems complicated the way I am explaining it.


  • 2.  RE: How to calculate a percentage on a Summary Report?

    Posted 05-01-2019 13:46
    Hi William,

    In order to get those calculations for the average across all of your records instead of getting a total of all the averages for each individual records you actually need to create a summary/statistics table and use a relationship to bring up both of those numbers as a summary field in order to do math on the total values. In your instance you would need a table along the lines of Monthly Ticket Types with records that contain a text field set up for each possible combo you want to report off like:

    Monthly Combo Field:
    January-2019-Landscape
    January-2019-Maintenance
    January-2019-Misc
    February-2019-Landscape
    February-2019-Maintenance
    February-2019-Misc
    March-2019-Landscape
    March-2019-Maintenance
    March-2019-Misc

    Then you would want that tables key field to be set to the Monthly Combo field you created and you have filled it with records to match what you want to report off of you are ready to add a formula field in your Tickets table called something like Monthly Combo Formula, and fill it with a formula that will take the date, the year, and the ticket type and put them together in a format like the records that exist on your Monthly Ticket Types table. That should fill each record in with its matching Monthly Ticket Type record. Then you can create a relationship between the two tables where Monthly Ticket Types have many Tickets and the related record field is your Monthly Combo formula. 

    Then once it looks like everything is set up correctly when you look at records and they seem to be correctly related you want to go into Monthly Combo formula then save your formula before setting the field to be just a plain text field instead of a formula. Once you do that your records are related and you can then go into your relationship between Tickets and Monthly Ticket Types and you can add summary fields to summarize the number of open tickets and closed tickets. Then once you have that set up you would go back into Monthly Combo and turn it back into a formula field and make sure your formula is still in there. Then any new records added will link up so long as there is a matching record added into the summary table. Then at that point to maintain your summary table you just need to make sure you add records for every combo you want to track, that usually means once a month adding a record for each type that month. 

    Once this is all set up then you can make formula fields on your Monthly Ticket Type table and do the math right off of your summary fields. If in the future you ever need to add new summary fields you would just do the same step of making the field a text field, add the summary field, and then turn it back into the formula. This is definitely a more advanced technique and can take some maintenance but it is the way to get to a summary calculation like that. I hope this information is helpful. 


  • 3.  RE: How to calculate a percentage on a Summary Report?

    Posted 05-08-2019 02:28
    Hi Evan,

    First of all, thank you for taking the time to write such a detailed reply.  I really appreciate it.  Wow, this seems much more complicated than it should be.  It makes me wonder if I didn't word the question correctly.  Let me just recap:

    I have a summary report which is set to run on the first day of each month.  The filter is all tickets opened in the previous month.  So for this month the report might look like this.

    Type                    Opened Tickets     Closed Tickets
    Landscape          318                          287
    Maintenance       91                            69
    Misc                    106                          80

    All I want is another column with the percentage of tickets closed [Closed Tickets]/[Opened Tickets].  Is the method you describe really the best way to do this?


  • 4.  RE: How to calculate a percentage on a Summary Report?

    Posted 05-09-2019 14:11
    Hi William,

    Currently yes that is the way you would set your app up in order to pull the percentages based off of all of your closed tickets divided by all of your opened tickets. Summary reports summarize up all the data across records but they do not have the ability to run calculations off of the summary values displayed. Calculated columns in the report instead run the formula off of individual records, which means the math doesn't come out the same.

    A relationship allows you to control how the data is aggregated before the math is done so you can control how you want it to all collect and then run the formulas on the summary fields. This then can give you a percentage based off something like a Type and Date combo.