SUM_PRODUCT

Description

SUM_PRODUCT takes two lists, multiplies the values in one list with values that are on the same index in the other list. It then adds up those multiplications, resulting in a single new value.
If the two lists within the brackets are of different size, SUM_PRODUCT will operate on all values with the same index and ignore all the values of the larger list that exceed the largest index of the smaller list.

Example: Two list with same length

In this examples two lists of three values each are used.
SUM_PRODUCT([1, 2, 3], [10, 20, 30])
The calculation SUM_PRODUCT performs is 1*10 + 2*20 + 3*30 and would return 140.

Example: List with different length

SUM_PRODUCT([1, 2, 3, 999], [10, 20, 30])
In this example, the first list has four elements while the second list has only three elements. The extra element 999 of the first list will therefore be ignored and, just as in the example before, 140 will be returned as result.

Example: Weighted Risk

Say the overall risk factor of a client is a number calculated from five individual risk factor numbers. Each individual risk factor is given a certain weight in the overall calculation, depending on how impactful we think that factor is.
Risk Factors
Weight
risk_factor_1
4
risk_factor_2
2
risk_factor_3
5
risk_factor_4
1
risk_factor_5
3
To calculate the overall risk score using SUM_PRODUCT, each individual risk factor score will be multiplied by its weight. The results are then summed up and returned as a single value.
p is Person
SUM_PRODUCT(
[
p.risk_factor_1,
p.risk_factor_2,
p.risk_factor_3,
p.risk_factor_4,
p.risk_factor_5
],
[4, 2, 5, 1, 3]
)
For the table above, if we assume a Person p has a set of risk_factors with the following corresponding values 3, 6, 2, 8, and 5, the returned result would be 57.
Last modified 4mo ago
Copy link
On this page
Description
Example: Two list with same length
Example: List with different length
Example: Weighted Risk