Calculation - Sum - Sum
This feature can be used to calculate the sum of values in a table, the sum of values between two or more tables, or the sum of the values in two or more variables for each case.
Usage
To use this feature, first select either variables in the Variables and Questions tab, or tables that you wish to sum.
- From the toolbar, select Automate > Browse Online Library > Calculation > Sum.
Example
Consider the following three input tables, each consisting of a single column of data.
When the feature is run with all three inputs selected, the result is the following output table.
By default the sum is still computed for the row with missing data (Jan-19) and Jul-19 is excluded from the output because it only appears in one input. These settings can be changed in the Object Inspector of the output, refer to the Inputs below.
Options
The output showing the results of the calculation has the following options available in the Object Inspector.
Input The tables to be used in the calculation.
Calculate for inputs with incomplete data A combo box with three options,
- Yes (show warning) Any missing values in any of the inputs will be ignored in the calculation. If a missing value is present in the input then a warning that missing values being removed from the calculation will be presented to the user.
- Yes The calculation will have the same behaviour as above, except the warning about missing values being removed from the calculation will not be given.
- No Missing values are not removed before calculation and will propagate as missing values in the output.
Automatically match elements Only shown when there are multiple inputs to Input. This controls how and whether matching is done between the labels of the inputs. The default, "Yes - hide unmatched", will look for matching labels in the rows and columns of the inputs before proceeding with the calculation, and any rows/columns that are not contained in all the inputs will not be included in the output. See the Example. For a full description of the matching algorithm, see the Technical Details. "Yes - show unmatched" will also perform matching, but any unmatched rows (columns) will appear in the output as rows (columns) of all missing values. Selecting "No" for this option will cause any labels in the data to be ignored and not perform any matching. Selecting "Custom" will bring up two additional controls that allow for specifying the matching behavior for rows and columns separately.
Match rows Only shown if Automatically match elements is set to "Custom". Specifies the matching behavior when comparing row labels of the inputs. "Yes - show unmatched" and "Yes - hide unmatched" look for exact matches in the row labels in the inputs. "Fuzzy - show unmatched" and "Fuzzy - hide unmatched" perform fuzzy matching so that labels that differ only by a single character are considered to be a match.
Match columns Only shown if Automatically match elements is set to "Custom". The options are the same as Match rows, but control the matching between columns.
Rows to exclude Here you can type in row labels that should be excluded from the calculation.
Columns to exclude As above, but for columns.
Technical Details
When there are multiple inputs, inputs that contain only a single row (column) may be recycled to a matrix/table with the same number of rows (columns) as the other inputs. For example, if the supplied inputs are a table with three rows and two columns and another table with two rows and a single column, the single column will be expanded by rows into a table with three rows and two columns with each row identical to the original column.
When Automatically match elements is set to Yes - show unmatched or Yes - hide unmatched, both exact matches and fuzzy matches (as described above) are considered, and the order of elements may be permuted so that the names match. It also may transpose an input if, for example, the column names of one input match the row names of another input.
When Calculate for inputs with incomplete data is checked and the input to the calculation consists entirely of missing values, then the returned output value is set to missing data. Prior to 5th October, 2021 the returned output value was different in this situation. For example, consider the input into the calculation with the three values.
| A | B | C |
|---|---|---|
| NaN | NaN | NaN |
Then applying Sum to this input would return the value of zero before 5th October, 2021. After this date, the Sum function returns the output value of NaN in this situation.
How to apply this QScript
- Start typing the name of the QScript into the Search features and data box in the top right of the Q window.
- Click on the QScript when it appears in the QScripts and Rules section of the search results.
OR
- Select Automate > Browse Online Library.
- Select this QScript from the list.
Customizing the QScript
This QScript is written in JavaScript and can be customized by copying and modifying the JavaScript.
Customizing QScripts in Q4.11 and more recent versions
- Start typing the name of the QScript into the Search features and data box in the top right of the Q window.
- Hover your mouse over the QScript when it appears in the QScripts and Rules section of the search results.
- Press Edit a Copy (bottom-left corner of the preview).
- Modify the JavaScript (see QScripts for more detail on this).
- Either:
- Run the QScript, by pressing the blue triangle button.
- Save the QScript and run it at a later time, using Automate > Run QScript (Macro) from File.
Customizing QScripts in older versions
JavaScript
includeWeb('QScript Functions for Calculations');
includeWeb('QScript Functions to Generate Outputs');
applyCalculationOnSelections('Sum');
Calculation
Q Technical Reference
Q Technical Reference
Q Technical Reference > Setting Up Data > Creating New Variables
Q Technical Reference > Updating and Automation > Automation Online Library
Q Technical Reference > Updating and Automation > JavaScript > QScript > QScript Examples Library > QScript Online Library