topic Re: Range Sum in QlikView

Range Sum

narender123 — Mon, 13 May 2013 07:07:06 GMT

Hi All,

Could anybody tell me the range sum fuction.I dont know about it.when should we have to use it?

like:

RangeSum(Above(Sum(Amount),0,RowNo()))

RangeSum(Above(Sum(Amount),-1,RowNo()))

RangeSum(Above(Sum(Amount),1,RowNo()))

Thanks in advance.

Re: Range Sum

tresB — Mon, 13 May 2013 07:19:23 GMT

The best place to learn the basics is HELP which says:

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.

Then try a bit to play yourself with it. the concept will get clear.

Thanks.

Re: Range Sum

narender123 — Mon, 13 May 2013 07:29:21 GMT

Thanks for the reply.

I have already read this and understand.But still i don't get my answer as per my question.So could you tell me according to my question.

Thanks.

Re: Range Sum

er_mohit — Mon, 13 May 2013 07:38:11 GMT

hiii

from helps you know the concept of rangesum its clear to you

nw in this 3 expression let me tell you one by one

RangeSum(Above(Sum(Amount),0,RowNo()))--- In this your accumulation starts from ist value which it writes as it is. because you write the 0 for that value and then for others vlue in row it accumulates

RangeSum(Above(Sum(Amount),1,RowNo()))-- in this your commulative starts from 2nd row and it will make first row as zero i hav attached sample file

RangeSum(Above(Sum(Amount),-1,RowNo()))- in this case it pick up 2nd value for accumlation means takes two values to be accumulated in my example first it take 20 then 20+30 acc. to condition and add 20 bove gives 70 then it gives you then it takes 70 accumulate then 30 and 50 gives you 150

and then gives 110 because of adding 50 and 60 and then 60

so on

hope it helps

Re: Range Sum

narender123 — Mon, 13 May 2013 09:22:47 GMT

Thanks Mohit.

Re: Range Sum

owais_sarwar — Wed, 18 Apr 2018 05:04:25 GMT

Hello Mohit,

I am stuck in the same scenario which you have stated. I am still having a problem after applying this expression because i have multiple dimensions in a straight table. What should i need to do in this case.

I need to implement that if the user input the budget in the input box. The straight table should show the rows till the time it reaches the Sum(Bugdet) <= VBudget. In other words may be i need the running total till the condition reaches.

If have written the set expression with if statement i.e.

=If(RangeSum( Above(TOTAL Sum([Rate]/2) , 0 , RowNo(TOTAL))) <= VBudget ,Num(Sum([Rate]/2),'#,##0'))

And after writing this expression, the dashboard is stuck. There may be an issue with the sorting technique.Your help would be highly appreciated in this regard.

Screenshots attached.

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.