topic Between values in QlikView

Between values

danielact — Tue, 10 Jul 2012 19:53:47 GMT

I'm using a pretty complicated statement in an expression - here's a simplified version:

if(aggr(rank(aggr(sum(Loss_Amount),Dimension1,Year),4,1),Dimension1,Year)=1000,'X','Y')

Instead of saying equal to 1000, I'd like it to be between 996 and 1005. Is there an easy way to do this, or will I have to use an AND function? I'd really prefer not to have it calculate the aggr statement twice.

Re: Between values

MayilVahanan — Tue, 10 Jul 2012 20:35:14 GMT

Hi,

You can use variable like

vTest =

(aggr(rank(aggr(sum(Loss_Amount),Dimension1,Year),4,1),Dimension1,Year)

And then use like this,

if(vTest > 995 and vTest < 1006 ,'X', 'Y')

Hope it helps

Re: Between values

Tue, 10 Jul 2012 21:44:04 GMT

Here's something that works with simple field names to test if they are between two values. For your more complex expression it will need some trial and error... Begin by defining a variable named check_between with this value

if( ($1) >= ($2) and ($1) <= ($3), 1, 0) Then, if you wanted to test if a field named myfield was between 996 and 1005 you would write the expression like this =if( $(check_between( myfield, 996, 1005), 'X', 'Y')