Expression wont work on highest geographical hierarchy

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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] )

[jonathandienst]

Hi

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)