Forum Discussion

JoshCollins's avatar
JoshCollins
Qrew Assistant Captain
4 years ago

Can I Summarize a Numeric Field Within Its Own Table?

Hello,

Is it possible to summarize a numeric field within its own table?

For instance, I have an "Properties" table with fields [Build Cost] and [SqFt]. Build Cost is currency and Sqft is standard numeric. 

I have another Formula - Numeric field called [Build Cost Per SqFt] which uses the formula [Build Cost] / [SqFt].

This formula calculates the Build Cost per SqFt for each Property record.  However, I need to calculate an average Build Cost Per SqFt for all records in the table...not individually. This new "global" field will be used in calculations to create projections, etc.

Am I overlooking a really simple solution to this?



------------------------------
Josh Collins
------------------------------
Formulas and functions
Relationships
  • JohnHarvey2's avatar
    JohnHarvey2
    Qrew Member
    4 years ago
    Hey Josh - The short answer, in my opinion, is to summarize the data into another table and then pass the average value back down to the "Properties" table. 

    The long answer:
    You are wanting more than just the current record summarized, so you add a parent table that summarizes the [Build Cost] and [SqFt].  That summary table will have two fields that contain the summary of the "Properties" table.  Then create your average calculation field on that table.

    Lastly, take the average calculation field and pass it back down to the "Properties" table.  It may feel like a lot of effort for a simple number, but it also gives you some good flexibility.  For example, you may want to only summarize records for the last 2 months, or ones that are open, etc...

    Cheers,


    John Harvey, MBA CBIP QBCE

    Regional Director

    Business Intelligence Reporting Services
    www.GetReportHelp.com

    John@getreporthelp.com           
    (Office) 208 572-0585

    (Cell) 406-334-9882

    Schedule a Meeting