topic Re. :Re: max + above functions return Null in QlikView

max + above functions return Null

Mon, 18 Apr 2011 10:20:54 GMT

Hi,

If I do

max(MyField - above(MyField))[/code ]I get "-" because above() return "-" for the first row. My question is to kow how to avoid this ?
Best regards,

Jérémy</body>

Re. :max + above functions return Null

vincent_ardiet — Mon, 18 Apr 2011 10:32:59 GMT

Hi Jeremy,

What do you want to do exactly ?

If you want to compare the max value of a field wihtin a dimension and the previous one (that means the max value of a field with the previous dimension value), the syntax is :

=max(myField)-above(max(myField))

And if you want to avoid the NULL value, you can use a test like :

=max(myField)-if(isnull(above(max(myField))),0,above(max(myField)))

Regards,

Vincent

Re. :max + above functions return Null

Mon, 18 Apr 2011 10:49:22 GMT

Hi Vincent,

MyFiled is a field with counter values. What I want to do is to find the maximum consumption between 2 counter values.

example:

MyField

0
10
12
16
...

I want fo find the value 10 because 10 - 0 returns the max

thanks

max + above functions return Null

Mon, 18 Apr 2011 12:42:20 GMT

You could make another column which has the difference between those numbers and take the max of that column 😉

MyField Diff

0 0
10 10
12 2
16 4

Max(diff) = 10

Kind Regards,

Martijn

Re. :Re: max + above functions return Null

vincent_ardiet — Mon, 18 Apr 2011 13:01:45 GMT

Try to compute a new column in your data script using the 'previous' function, for example :

test:

LOAD id, counter

FROM ...

join

load

id,

counter-if(isnull(previous(counter)),0,previous(counter)) as gap

resident test;

Then you can have the max(gap) in your report.

Regards,

Vincent