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)?
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.
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!
You need to replace the single select field with a link to table field.
Create a new table and name it Transaction types
Add 2 records to this table: primary and secondary
Change the type of the Transactions_type field to link to table and link to the new table
Select the item primary or secondary for each record.
Go back to the transaction types table and notice the additional field
Add a new field of the type formula and add the following formula
The result is the total score for each transaction type
- Use the SUM function to calculate the sum of all values in the column.
- Export the filtered data to a separate table.
- 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.
- Apply the formula to each row in the exported data to determine the percentage.