Variable Value

[#1]

I have written this formula to calculate mean value of a product in all the territories and stored it in a variable. But whenever i am selecting any territory that variable value is changing. But it should remain same irrespective of whatever selection i make. what could be the reason for the difference in value.

=

avg(aggr(
num(
((sum({$<%EPID={1},Segment={'Stucks'}, TERRITORY=>} (Volume))/sum({$<%EPID={7},Segment={'Stucks'},TERRITORY=>} (Volume)))-
((sum({$<%EPID={1},Segment={'Stucks'},TERRITORY=>} (Volume))-sum({$<%EPID={1},Segment={'Stucks'},TERRITORY=>} (Volume_Change))))/
((sum({$<%EPID={7},Segment={'Stucks'},TERRITORY=>} (Volume))-sum({$<%EPID={7},Segment={'Stucks'},TERRITORY=>} (Volume_Change)))))

,'#,##0.0%'), TERRITORY))

to make the variable value constant we can load this formula in the load script. But i dont know how to do that. Please Help

[jonathandienst · Accepted solution]

Hi

Hard to say without an example to test, but I suspect the problem is that there is no set expression on the avg() function itself, so the territory selection will affect the avg. Try

=avg({<TERRITORY=>} aggr(..))

Incidentally, the Num inside the aggr is doing nothing except making the statement more complex. If you want to format the results, the num should be the topmost expression.

=num(avg({<TERRITORY=>} aggr(..)), '0%')

Hope that helps

Jonathan

[#3]

Thanks Jonathan