Summarize multiple columns in a chart

Hi John,

For your math in this instance I just wanted to confirm would it be:

# of Records with age greater then 50 divided by # of employee records minus # of Records with age less then 30 divided by total # of employee records?

Yes.  I realize this is the average, however its two different values that is causing the issue

One way I have done this in the past is to do the calculation using a parent table with a master parent record. Essentially I set up a table so I can summarize the values across records up to it with a single Master Parent Record. Then you can go in and set all the existing records to be related to that 1 record. Build out a summary field for # of records with age great then 50, another summary field with # of records less then 30 and a final field to bring up total number of employees. This creates all of your summarized values in a single record, which means you can then do a calculation on the summarized values. Once the existing records are all related to the single existing Master Parent Record you can then set the related Master Parent Record field in the Employees table to default to 1 and all new records will automatically associate. 

Thanks.  I'm a little confused with how the table relationships are setup. Is there a step by step tutorial I can access that explains this? I've attempted a relationship but the fields are never populated nor do they contain the correct information.

Hi John,

I don't have a step by step tutorial for setting up the relationships I can link you to but I can walk you through the steps in a little more detail. 

What I usually start off with in this instance is to create a new table called Master Parent Table. Once that table exists I create a single record just named Master Parent. With that in place I then create a relationship so that Master Parent can have many employee records. This will create for you a new reference field that should be name Related Master Parent in every one of your Employee records that will be blank.

From there you would go into the Employee records you have and you can either manually edit the existing records you have to all relate back to your Master Parent record. Alternatively there is a handy little trick you can do to speed this up if you have a large number of employees. You can go into the field properties for this new reference field and change the field type to a formula - numeric field. Once you do that you can set the formula box up correctly by just putting in the number 1 and then saving the field. Now every record is automatically related to your  Master Parent record since it is record #1 in the parent table. 

Then what I typically do after this is open up the Related Master Parent field in the Employees table again and switch it back to a standard manual entry numeric field. It will still show all the existing records as related to 1. Then you will set that fields default value to be 1, so that by default all new records are related to Master Parent record 1. 

This leave you all set to go into the relationship settings for that table, open up the relationship of Master Parent Record to Employees and add the summary fields you need to the new parent table. This will cause your Master Parent table to have a field for # of records with age over 50, # of records with age under 30, and total number Employee records all in one record that you can then do your division off of. It does take some work to get the structure in place but once done it will maintain itself unless you wish to make changes. I would encourage you if you need more focused help or think a screen share session to go over making a Master Parent record to reach out to the Care team via a support case. You can create a support case here.