Loading relative AVG

[marcel_olmo]

Hi guys, I'm trying to the following in Qlikview :

ID	VALUE	RELATIVE AVG
1	1	0
2	2	0
3	3	0
4	4	0
5	5	2,50
6	6	3,50
7	7	4,50
8	8	5,50
9	9	6,50
10	10	7,50

As you see, I want to calculate dynamically the average of the previous 4 numbers of the field VALUE.

For example in ID =5, the previous four numbers does a sum of 10, divided into 4 rows --> 2,50

I'm trying to do it with a resident table using rangeAvg() but i bet I'm doing something wrong. Someone can help me?

I attack an excel with the example.

Thanks in advance.

Regards, Marcel.

[swuehl]

Maybe something like

LOAD ID,

        VALUE,

       RangeAvg( Peek('VALUE', -1), Peek('VALUE', -2),Peek('VALUE', -3),Peek('VALUE', -4)) as [REALATIVE AVG]

FROM ...;

[marcel_olmo]

Thanks swuehl for your quick response,

the point is that I want to do it dinamically. If i want to do it for the relative 20, I have to do it manually?

Regards, Marcel.

[afurtado]

Hi Marcel.

Using swuehl‌ expression ,  maybe this can help....

You can change vQtRelative for 4 or 20 or .....

vQtRelative = 4;

Let vPeek       = 'if(recno()>'&$(vQtRelative)&',RangeAvg(';

Let vFieldName  = 'VALUE';

For A = 1 to '$(vQtRelative)'

    vPeek   = '$(vPeek)' & 'peek('&chr(39)&'$(vFieldName)'&chr(39)&','&($(A)*-1)&')'&if('$(A)'='$(vQtRelative)','),0)',',');

Next

LOAD

     ID,

     VALUE,

     $(vPeek)          as [REALATIVE AVG NEW],

     [RELATIVE AVG]

FROM (ooxml, embedded labels, table is Hoja1);

[afurtado]

ID	RELATIVE AVG	VALUE	REALATIVE AVG NEW
1	0,00	1	0
2	0,00	2	0
3	0,00	3	0
4	0,00	4	0
5	2,50	5	2,5
6	3,50	6	3,5
7	4,50	7	4,5
8	5,50	8	5,5
9	6,50	9	6,5
10	7,50	10	7,5

[afurtado · Accepted solution]

vQtRelative = 2

ID	RELATIVE AVG	VALUE	REALATIVE AVG NEW
1	0,00	1	0
2	0,00	2	0
3	0,00	3	1,5
4	0,00	4	2,5
5	2,50	5	3,5
6	3,50	6	4,5
7	4,50	7	5,5
8	5,50	8	6,5
9	6,50	9	7,5
10	7,50	10	8,5

[marcel_olmo]

Thanks Alessandro, it looks great! Regards, Marcel.