Strange behaviour when sum "<" vs. ">"

[Anonymous]

Dear All,

i found a rather strange behaviour when trying to sum values based on a if < vs. if >

I am trying to visulize the +/- deviance per month(y) compared to a fixed month(x).

So i basically build two columns per year if(x<y,sum(x-y)) / if(x>y, sum(x-y)).

The problem is the QV does not sum the if(x>y, sum(x-y)).

Is this a bug? I can't see any reason why no sum is shown for this expression. I tried to show this in the attached QVW.

I'd appreciate if you'd have a look at it!

Thank you

BR

Andreas

[swuehl · Accepted solution]

Andreas,

in a pivot table, the totals are evaluated as expression total, so your if() statements are not evaluated per dimension REGION and then summed up (sum-of-rows), but the if() statement is evaluated in global context.

You can create a sum-of-rows using advanced aggregation (see also the HELP for a sample on the sum-of-rows using advanced aggregation), your expression then looks like

sum(aggr(

if(Sum({$<MONTH={201101}>} total<REGION> Value) < sum(Value), Sum({$<MONTH={201101}>} total<REGION> Value) - sum(Value))

,REGION,MONTH))

See also attached,

Stefan

[Anonymous]

Thank you so much! That solved my issue!