Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Champion III
Champion III

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.
Highlighted

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