Can value of footer aggregation be used in formulas

1

I have a table with a filtered view. I am trying to get the sum of all numbers in a column? How do I achieve this? There is the footer aggregation. But I cannot seem to export that value to another table for computation purposes (for eg. to determine the % value of a field. I want to divide the number in a particular field by the sum of all values. However, how do I get that sum of all values, using sum function or otherwise)?

2

Hi,

You cannot export or use the footer aggregation. However, you can generate the same result in another table using nested formulas.

This will mostly require a combination of the filter(), sum() and count() function where the filter function allows you to select only records that meet certain conditions.

The exact formula depends on your particular case.

3

Thanks for the response. I am still struggling to get this to work. I have one table which has among other fields, two relevant fields for the purposes of this discussion- (a) Transaction_type -which is a Single Select field and users can either choose its value as ‘primary’ or ‘secondary’; (b) Score- which is a number.

What I am trying to achieve is display the sum of all the values of ‘Score’ which has Transaction_type ‘Primary’.

Alternatively, even a simple sum of all the values of ‘Score’ would do.

thanks for your help!

4

You need to replace the single select field with a link to table field.

  1. Create a new table and name it Transaction types

  2. Add 2 records to this table: primary and secondary

    image
    image764×276 7.99 KB

  3. Change the type of the Transactions_type field to link to table and link to the new table

    image
    image1392×850 63 KB

  4. Select the item primary or secondary for each record.

  5. Go back to the transaction types table and notice the additional field

    image
    image830×286 16.5 KB

  6. Add a new field of the type formula and add the following formula sum(lookup('Transactions','Score'))

The result is the total score for each transaction type

image
image946×284 17.8 KB

5
  1. Use the SUM function to calculate the sum of all values in the column.
  2. Export the filtered data to a separate table.
  3. Calculate the percentage using a formula: (Value / Sum of all values) * 100. Replace “Value” with the specific value and “Sum of all values” with the sum calculated using the SUM function.
  4. Apply the formula to each row in the exported data to determine the percentage.