topic What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3)) in QlikView

What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

Sat, 27 Dec 2014 12:35:23 GMT

What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

Plz tell

Re: What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

its_anandrjs — Sat, 27 Dec 2014 13:30:25 GMT

Hi,

RangeAvg is ths Aggregate function and it is used for calculate the average of the expression of current row. But it is calculate the average of Sum([Billing Sales Amount_NETWR]) above 2 rows of the given table.

Regards

Anand

Re: What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

SunilChauhan — Sat, 27 Dec 2014 14:40:08 GMT

it will sum the 3 previous value of Billing salesamount_Netwr

for example you have data like below

ID,Billing salesamount_Netwr

1,100

2,200

3,300

then it will give you 600 as output.

or for more clear

ID,Billing salesamount_Netwr

1,100

1,300

2,200

2 200

3,300

then sumof Billing salesamount_Netwr give you below result

1.400 ----------------------> sum of ID=1 amount(100+300)

2.400------------------------> sum of ID=2 amount(200+200)

3.300------------------------> sum of ID=3 amount(300)

and

rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

will give you below result

1.400 ----------------------> sum of current amount+ 2 previous amount i.e for ID (400+0+0) as no 1+2 previous value

2.800 ----------------------> sum of current amount+ 2 previous amount i.e for ID (400+400+0) as no 2 previous value

3.1100 ----------------------> sum of current amount+ 2 previous amount i.e for ID (300+400+400)

hope this helps

Re: What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

its_anandrjs — Sat, 27 Dec 2014 16:36:38 GMT

Hi,

Assume this example for the RangeSum and RangeAvg explanation

Sample_Int:
LOAD * INLINE [
Month, Sales
Jan-09, 55
Feb-09, 39
Mar-09, 33
Apr-09, 56
May-09, 55
Jun-09, 70
Jul-09, 57
Aug-09, 29
Sep-09, 34
Oct-09, 41
Nov-09, 12
Dec-09, 11
];

And in the front end take

Dim:- Month

Expre:- =RangeAvg(above(Sum(Sales),0,3))

And Out put you get

Month	RangeAvg(above(Sum(Sales),0,3))	Sum(Sales)
Jan-09	55.00	55
Feb-09	47.00	39
Mar-09	42.33	33
Apr-09	42.67	56
May-09	48.00	55
Jun-09	60.33	70
Jul-09	60.67	57
Aug-09	52.00	29
Sep-09	40.00	34
Oct-09	34.67	41
Nov-09	29.00	12
Dec-09	21.33	11

=RangeAvg(above(Sum(Sales),0,3)) this calculates the Range average below 3 rows

For Jan-09 (55)

For Feb-09 (55, 39)

For Mar-09 (55,39,33)

For Apr-09 (39,33,56)

Till this ways

Regards

Anand

Re: What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

Anonymous — Sat, 27 Dec 2014 20:11:21 GMT

Hi,

rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

Above expression calculates avg of rolling n months of Billingsales,0 value means current row and 3 means counting from current to updwards 3 rows are taken into consideration to calculate avg of range specified.

please refer to below link for rolling n months example,which can be implemented for any RangeXXX functions:

http://community.qlik.com/docs/DOC-4252

Re: What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

Sun, 28 Dec 2014 07:37:29 GMT

rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

means:

Take the current Row [Billing Sales Amount_NETWR] and TWO(2) rows above the current position (to make 3 rows in total) and then compute the Average based on the SUM of them.

ie:

20 X if this is the current position, it will do average of 20 + 10 /2

30 X if this the current position, it will do average of 30+20+10 /3

Hope this helps.