topic Re: How and when to use RangeSum in QlikView

How and when to use RangeSum

Wed, 25 Nov 2020 16:16:04 GMT

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

Regards,

Dawar

Re: How and when to use RangeSum

marcus_sommer — Sun, 03 Jul 2016 05:30:08 GMT

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

Re: How and when to use RangeSum

Chanty4u — Sun, 03 Jul 2016 10:41:10 GMT

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.

Re: How and when to use RangeSum

Peter_Cammaert — Sun, 03 Jul 2016 11:39:19 GMT

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