topic Re: Loading relative AVG in QlikView

Loading relative AVG

marcel_olmo — Sat, 11 Jul 2015 17:55:06 GMT

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

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 — Sat, 11 Jul 2015 18:04:05 GMT

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 — Sat, 11 Jul 2015 18:14:46 GMT

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 — Sat, 11 Jul 2015 18:40:39 GMT

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)',',');

LOAD

ID,

VALUE,

$(vPeek) as [REALATIVE AVG NEW],

[RELATIVE AVG]

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

afurtado — Sat, 11 Jul 2015 18:44:03 GMT

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 — Sat, 11 Jul 2015 18:45:19 GMT

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 — Sat, 11 Jul 2015 19:00:17 GMT

Thanks Alessandro, it looks great! Regards, Marcel.