Sum

Returns the mathematical sum operation on a list of numbers.

  • Returned Output: Numeric
  • Library: PQL \ Common \ Statistical
  • Version: 2018.00.000
  • Compatibility:
    • Can be combined with and other PQL function throughout the application.
    • It CANNOT be used with MDX or VBA functions. But it can be used on MDX-based content in other parts of the application.

Syntax

Sum( <Numeric List> )

or

Sum( <Numeric N> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The list of numbers can be typed in as a comma delimited list or inserted via an array.
  • If the method is used in other parts of the application, the input parameters can be changed
    • In Model, the inputs can be column names - which means to add the values for those columns row by row
    • In Formulate, the inputs can be Data points, other functions or members
    • In the dynamic querying tools (like triggers, infographics and dynamic text), the inputs can be static values or other methods
Different Function types
  • This function is like the Excel sum function - calculating a summation across a list of items.
  • If, instead, you wish to change the aggregate of a numeric column in the database to sum, use this function
  • If you wish to find the sum for the elements in a specified SET or LIST from a hierarchy, use this semantic function.
  • The alternative approach to the sum function is to use the simple plus "+" operator. Sum functions can be more readable and easier to use when the underlying list is dynamic.

Examples

This example simply sums the 4 values 3 to 6 together (18)

sum(3,4,5,6)

 

This example sums 3 data points inside the dynamic calculation engine. Each data points is read from the underlying query.

sum(data(0,0),data(1,1),data(4,1))

So, using the grid below, this would return the sum of 486402.93, 438248.31 and 416994.12.

This example, using the same grid above, would sum all the values in column 2 (numbering is zero-based). The DataSetColumn function returns an array (list) of values from column 3 in the grid above.

sum(dataSetColumn(2))

So it would add 23.89%, 23.59% etc all the way through to 22.52% (257.29%)

 

This example, used in the modeling tool (as shown below), would add the values from the sales and cost columns together. Because it is processed on a row basis, the calculation would reach a value from each column per row to find the summed outcome. So it would add 920.5 and 1315, followed by the next row with the same input values.

sum ([Sales],[Cost])

 

This example sums 2 'members' inside the Formulate tool. This formula will always provide the summation of "Bachelors" and "Graduate Degree" together,

sum([customerProfile].[Education].[Bachelors],[customerProfile].[Education].[Graduate Degree])

Using the graphical formula builder, this example would appear as follows in the Formulate editor.