Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How and when to use RangeSum

Can anyone help me in understanding RangeSum function with examples? When should we use it?

Regards,

Dawar

3 Replies
marcus_sommer

A range-function returned for each not numeric parameter 0 and therefore the function returned always a valid result. For example a calculation of: 1 + 2 + 'A' or 2 + NULL() will be fail and return NULL and the same within a rangesum(1,2,'A') returned 3 respectively rangesum(2, NULL()) returned 2.

Therefore a range-function or quite similar the alt-function is to use where you need to make sure that your calculation returned always a valid result.

- Marcus

Chanty4u
MVP
MVP

hi,

Range functions will replace the following Chart General Numeric Functions: numsum, numavg, numcount, nummin and nummax, which should now be regarded as obsolete.

rangesum(expr1 [ , expr2, ... exprN ])

Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0.

The argument expressions of this function may contain Chart Inter Record Functions with a third optional parameter, which in themselves return a range of values.

Examples:

   

rangesum (1,2,4)returns 7
rangesum (1,'xyz')returns 1
rangesum (null( ))returns 0
rangesum (above(count(x),-1,3))returns the sum of the three results of the count(x) function evaluated on the row below the current row, the current row and the row above the current row.
Peter_Cammaert
Partner - Champion III
Partner - Champion III

In addition to what Marcus already explained, you can put it very simply like this: what is the difference between Sum() and RangeSum() and '+'. They all seem to add values right?

  • + will add the left and right operand/expression. Invalid or Null operands will propagate into the result making it worthless. See Marcus' post.
  • Sum() will add all active values stored in its single expression parameter. Meaning that you can only specify a single expression as parameter but Sum() will calculate the expression/field for all active dimension values and add them together. I like to call it a "Vertical Add"
  • RangeSum() will add all of the values in the parameter list, thereby replacing any invalid or null parameter with a 0 value. See Marcus' post. I like to call this one a "Horizontal Add".

Note that in a load script, + and RangeSum() can be used "everywhere", but Sum() can only be used in a LOAD statement where some form of aggregation is either active (GROUP BY) or implied.

Best,

Peter