Supercharging Summary Reports with New Aggregation Options

By Brian Cafferelli posted 04-19-2021 09:31

  

Supercharging Summary Reports with New Aggregation Options

Quickbase helps businesses overcome tough process challenges every day. We do this by giving citizen developers the right tools to perfect their team’s unique processes. One of those tools is summary reports, making it easy for you to present complex business metrics organized by category. However, if you’ve worked extensively with summary reports you may notice some calculations not working as you expect. We’ve introduced a new report feature called summary formulas, to allow you to perform calculations on aggregated data without needing to create additional tables.

 

The Averages of Averages Problem

When you use a report formula on a summary report, Quickbase first performs your calculation for each individual record. Then the result is aggregated. However, for many types of data analysis, it’s actually the reverse that’s needed. First, each set of data should be aggregated and then the calculation should be performed on the aggregated data. This most often occurs when calculating averages.

Quickbase app builders have worked around this using a clever setup called Statistics Tables. This makes use of summary fields and formula fields together to create the proper calculation on a parent table rather than doing so on a summary report. This approach is effective but is time-consuming and not very intuitive.

We learned about the Averages of Averages Problem through feedback many of you shared with us. This was a tricky problem for us to address. We wanted to enable you to perform calculations on aggregated sets of data. However, we also wanted to avoid changing the math that our builders have been using on summary reports for many years.

To solve this problem, we’ve added a new type of calculation to summary reports, called summary formulas.

 

Summary Formulas

Like report formulas, summary formulas allow you to add custom calculations to your report. However, these new types of formulas perform calculations on aggregated sets of data instead of individual values.

To create a summary formula, first identify which fields you want to aggregate. Then, write your formula as you normally would, using those aggregated fields. Notice that when building a summary report, you now have the option to use Report Formulas, Summary Formulas, or a mix of both. Summary Formulas are exclusive to summary reports for now - but we plan on extending them to charts in the future as well.

 

Example: Calculating Gross Margins

There’s a wide variety of calculations summary formulas apply to. But let’s consider a relatively simple example: averaging gross margins. For those who have not worked with them before, a gross margin is a common method for measuring profit. It’s calculated using this formula:

 


If we tried to add a report formula to a summary report to make this calculation, in this case we get 16%.


However, if we insert the Total Revenue and Cost of Goods into the formula above, the result is 14%. This is the proper result we’re looking for:

 


We can calculate the correct gross margin using summary formulas, in three steps. First, we’ll define the summary variables. Then we will write the formula. And finally, we’ll add the formula as a new report column to summarize.

So let’s define two summary variables, one for Total Revenue and the other for Cost of Goods:

 

 


Next, we’ll write the formula:

 


And finally, we’ll add the summary formula to display on the report:

  


And when we view the report, we get the 14% we need:

 


Quickbase Runs on Quickbase

Aleks Cybulska is a data analyst who works for Quickbase. They often create dashboards to monitor progress against our company goals. However, to create the necessary calculations, they needed to create extra Statistics Tables. You can read this community thread to learn how summary formulas are already making Aleks’s job easier.

 

Putting it all together

We’re excited to supercharge the power of summary reports in Quickbase. If you’ve created Statistics Tables, you should be able to remove them from your app and use summary formulas instead. This has the potential to significantly streamline some of your most complex apps. And the next time you need to perform a calculation on aggregated data, you’ll be able to do so much faster. This was all possible because resourceful app builders keep pushing the limits of what they can accomplish with the Quickbase platform. Thank you for your contributions and your persistence.

You can learn more about creating summary reports on our help site.

Permalink