topic Re: Expression wont work on highest geographical hierarchy in QlikView

Expression wont work on highest geographical hierarchy

Anonymous — Fri, 30 Jan 2015 11:43:14 GMT

Hi everyone,

Im having trouble with the following expression: = if ( [Value] < 5, 5, [Value] )

essentialy, this expression states that if the value is smaller than 5, make it 5, otherwise give the original value.

this expression works fine on my lowest geographical level, which is Store.

each store falls within an Area.

now, if I do the same calculation using Area as the dimension, the expression wont work, because the Area's values accumulates to more than 5.

What can I do validate the same expression on Area level?

Thanks

Re: Expression wont work on highest geographical hierarchy

Anonymous — Fri, 30 Jan 2015 11:48:01 GMT

So you still want to compare the if ( [Value] < 5, 5, [Value] ) at Store level when using Area as dimension or you want to compare it with accumulated value at area level ?

if possible share your sample qvw and expected result.

Re: Expression wont work on highest geographical hierarchy

Anonymous — Fri, 30 Jan 2015 12:10:11 GMT

Hi, unfortunately I cant share the app, as it contains information regarding the company I work for.

But yes, I want to compare that same expression on store level, whilst using Area as dimension

Re: Expression wont work on highest geographical hierarchy

Anonymous — Fri, 30 Jan 2015 12:19:28 GMT

Try using aggr function may it will hep you ,,, in place of SUM you can also try Only() function

=if(aggr( sum(Value), Area , Store)<5,5, [Value] )

Re: Expression wont work on highest geographical hierarchy

jonathandienst — Fri, 30 Jan 2015 12:26:05 GMT

you probably need an Aggr expression, on the lines of:

Sum(Aggr(If(Value < 5, 5, Value), Store, Area, .... <other fields of hierarchy>...))

HTH

Jonathan

PS - a more efficient way of calculating if(Value < 5, 5, Value) is RangeMin(Value, 5)