Range Function

[#1]

Hi everyone.

I've been reading up on the different row functions to help me accomplish my task by modifying the standard rangeavg syntax but the standard syntax seems incorrect?

I am trying to find the average of the 12 rows above the current row (not including it). Qlikview does not agree with the syntax of the rangeavg though.

Please can you assist me. Would also appreciate any advice if you feel there's a better way to approach this task. Later on I will be building on this to check if there are nulls or 0 values in any of the previous 12 rows and then dividing by less than 12 rows (in other words avoiding affecting the average by only dividing by rows that had a value greater than zero)

Many thanks.

"Prov Summary Temp":

Load
[Month Start],
[Scheme],
[Scheme Product],
[Claim Count],
rangeavg(above(sum([Claim Count]),1, 12)) as [Prov Avg]

[swuehl]

above() is a chart inter record function and can only be used in a chart, not in the script.

You can use multiple instances of peek() as arguments to rangeavg() to achieve what you want.

edit: For example:

LOAD *, rangeavg( peek(Value),peek(Value,-2),peek(Value-3))  as AVG  ;

LOAD recno() as Value

AutoGenerate 100;

[#3]

Hi!

Thank you for this!

I will incorporate your suggestion into my query and let you know how it goes..

Many thanks!

[#4]

Hi.

I tried out your suggestions but for some reason, even a basic peek function does not yield the correct results in my script.

It brings up very arbitrary values and nothing from the Claim Count column.

Load

[Month Start],

[Scheme],

[Scheme Product],

[Claim Count],

peek('Claim Count') as [peek test]

rangeavg(peek('Claim Count'), peek('Claim Count', 2), peek('Claim Count', 3)) as [Prov Avg]

Resident "Prov Summary"

Order by [Month Start],

         [Scheme],

         [Scheme Product];

Would appreciate your help.

Thank you.

[hic]

Use

rangeavg(peek('Claim Count'), peek('Claim Count', -2), peek('Claim Count', -3)) as [Prov Avg]

instead. (The minus signs.)

HIC

[MayilVahanan]

Hi

Try like this

rangeavg(peek('Claim Count'), peek('Claim Count', -2), peek('Claim Count', -3)) as [Prov Avg]

2   -> Takes Next Value
-2  -> Takes Previous Value

[#7]

Hi Henric / Mayil.

Thank you both for your assistance.

It still does not seem to work on my side. Below is my code and the results I am getting.

"Prov Summary Temp":

Load

[Month Start],

[Scheme],

[Scheme Product],

[Claim Count],

peek('Claim Count') as [Peek One Row Back],

peek('Claim Count', -2) as [Peek Two Rows Back],

peek('Claim Count', -3) as [Peek Three Rows Back],

rangeavg(peek('Claim Count'), peek('Claim Count', -2), peek('Claim Count', -3)) as [Prov Avg]

Resident "Prov Summary"

Order by [Month Start],

         [Scheme],

         [Scheme Product];

Results:

Month   Start	Scheme	Claim Count	[Peek One Row   Back]	[Peek Two Rows Back]	[Peek Three   Rows Back]	Prov Avg
		362	608	804	1013	808
2013/01/01	Forevermed	52	80	120	170	123
2013/02/01	Forevermed	26	51	100	133	95
2013/03/01	Forevermed	38	72	128	152	117
2013/04/01	Forevermed	37	66	85	112	88
2013/05/01	Forevermed	42	81	95	92	89
2013/06/01	Forevermed	45	62	79	108	83
2013/07/01	Forevermed	41	69	87	102	86
2013/08/01	Forevermed	43	64	78	104	82
2013/09/01	Forevermed	37	62	30	39	44
2013/10/01	Forevermed	1	1	2	1	1

[#8]

Hi everyone.

I figured out what was wrong - my column orders were affecting the peek.

Thank you.