topic Re: Range Function in QlikView

Range Function

Thu, 17 Oct 2013 13:46:57 GMT

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]

Re: Range Function

swuehl — Thu, 17 Oct 2013 14:00:45 GMT

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;

Re: Range Function

Fri, 18 Oct 2013 06:43:23 GMT

Hi!

Thank you for this!

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

Many thanks!

Re: Range Function

Fri, 18 Oct 2013 08:41:07 GMT

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.

Re: Range Function

hic — Fri, 18 Oct 2013 09:35:18 GMT

Use

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

instead. (The minus signs.)

HIC

Re: Range Function

MayilVahanan — Fri, 18 Oct 2013 09:37:25 GMT

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

Re: Range Function

Fri, 18 Oct 2013 10:46:00 GMT

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

Re: Range Function

Fri, 18 Oct 2013 14:01:17 GMT

Hi everyone.

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

Thank you.