Dividing the results of two column totals
So I have reports built in my app where one shows a % but is actually an average if the % complete from each of my line items. I also have a report already showing in $ amount the current earned valu...
Forum Discussion
Hi George,
I'm going to guess this is a summary report where the two columns you would like to see the average of are summed up data from across your records? Unfortunately, as you realized the fields in the summary report will run the math you are doing on each record and then average those results which can result in wildly different math than you are looking for. Currently when you are looking to do those summary based calculations you need to implement a table and relationship to help you bucket and summarize up the data so it can all be in one record. For example if you wanted to run that kind of math on sales metrics for a month and you get records ever day you would build a months table to relate all those records to and summarize up the math and then calculate on the individual month record, which is a little more complicated. Tom from our SC team did a series of analytics articles that walk through the premise:
Improve your monthly insights by implementing a Months table
Quickbase Analytics: Summary Tables
These two articles walk through setting up the framework to be able to build a summary table and run these kinds of analytics in Quickbase. While it doesn't help you today our product team is working on enhancements to our formula language which will allow you to run those kinds of analytics without building the tables. One of or PMs, Harrison, recently wrote an article talking about the upcoming changes to power up formulas to unlock the use case you have we just don't have a specific release date yet. So there is an easier solution on the horizon. I hope this information is helpful.
#reportsandcharts
#Formulasandfunctions
------------------------------
Evan Martinez
Community Marketing Manager
Quickbase
------------------------------
I'm going to guess this is a summary report where the two columns you would like to see the average of are summed up data from across your records? Unfortunately, as you realized the fields in the summary report will run the math you are doing on each record and then average those results which can result in wildly different math than you are looking for. Currently when you are looking to do those summary based calculations you need to implement a table and relationship to help you bucket and summarize up the data so it can all be in one record. For example if you wanted to run that kind of math on sales metrics for a month and you get records ever day you would build a months table to relate all those records to and summarize up the math and then calculate on the individual month record, which is a little more complicated. Tom from our SC team did a series of analytics articles that walk through the premise:
Improve your monthly insights by implementing a Months table
Quickbase Analytics: Summary Tables
These two articles walk through setting up the framework to be able to build a summary table and run these kinds of analytics in Quickbase. While it doesn't help you today our product team is working on enhancements to our formula language which will allow you to run those kinds of analytics without building the tables. One of or PMs, Harrison, recently wrote an article talking about the upcoming changes to power up formulas to unlock the use case you have we just don't have a specific release date yet. So there is an easier solution on the horizon. I hope this information is helpful.
#reportsandcharts
#Formulasandfunctions
------------------------------
Evan Martinez
Community Marketing Manager
Quickbase
------------------------------