summary report formulas

As a Data Analyst at Quickbase, I'm often asked to create reports and scorecards to track our performance over time. In most cases, it's easy to provide this data with the summary report feature – it can be done simply by adding the numeric fields to the report, choosing how to aggregate them (sum, average, etc.) and grouping by the appropriate date fields. But what can you do when you're asked to perform additional calculations on those aggregated metrics? Some people refer to this as the averages of averages problem, even though it is much more!

There are plenty of these scenarios in every department: lead conversion in marketing, quota attainment in sales, net retention in finance, just to name a few… Also, different business units often have their own targets, and they need reports to inform how they are executing against them on a monthly, quarterly and annual basis. Since this was not possible on summary reports, my usual workaround was creating new tables, adding summary fields and doing the final calculations there. 

I'm very excited that now our product has a new feature called summary report formulas, which makes those calculations much easier than before, and will save me a lot of time and effort! Now, all I need to do is create a summary report on my source table, choose how to group my data and follow 3 simple steps: 

1. Define summary variables –  

• • Simple example – Calculating win / loss ratio in Sales. Values to define:  
    • Total # of won deals 
    • Total # of lost deals.  
  • Complex example: Calculating utilization ratio against target for a customer support center. Values to define: 
    • Duration of direct customer support (hours) 
    • Duration of additional customer support tasks (hours) 
    • # of vacation hours 
    • # of sick leave hours 

2. Write summary report formula –  

• • Formula to calculate win / loss ratio: 
    • Total # of won deals/ (Total # of won deals + Total # of lost deals) 

• • Formula to calculate utilization ratio against target (assuming that the report is grouped by week and by rep, each support rep works 40h a week and is expected to have a utilization of 60%): 
    • (Duration of direct customer support (hours) + Duration of additional customer support tasks (hours)) / (40 - # of vacation hours - # of sick leave hours) / 0.6

 
3. Add my new field(s) to the report.  

 

This way, instead of creating separate tables with calculations for each time period, I can build all of my reports with percentage calculations on the same table. Stakeholders won't have to wait for me to create a few tables, join them, add summary fields, and then write the formulas to get the results. Thanks to summary report formulas, I will be able to deliver data to the business in minutes, rather than days and hours! 

This feature is currently in beta. You can express interest in our beta registration application. We are inviting people into the program in waves but if you believe you have a use case that may enable you to provide valuable feedback on this feature, please reach out to your account team. 

------------------------------
Aleksandra Cybulska
------------------------------