Convert the if statement into a set analysis expression

[divya_anand]

Hi,

Can someone help me write the following expression using set analysis? I have to ignore the selections in fields Year & Month, for which I have to use Set analysis.

if(Value>0,Value, Value_FIRST - above([Total quantity]))

here [Total quantity] is another column in the straight table.

FYI- the expression for [Total quantity] is

fabs(Sum(
{$<Year=,Month=,
code={100,201,306,500}
>}
ValueZ))

Thank you.

[divya_anand]

I tried to incorporate set analysis in this way-

sum({<Year=,Month=>}if(Value>0,Value,Value_FIRST - above([Total quantity])))

However, this throws up an error which I believe is because of above([Total quantity]).

Any idea on this?

[avinashelite]

You have to use if condition here ...try like this

if(Value>0,sum({<Year=,Month=>}Value), Value_FIRST - above([Total quantity]))

[jonathandienst]

I don't think you can apply multi-row aggregations (like Sum()) to in-table column references. They work on a row by row basis.

Depending on what you are trying to do, you could use the expression for the column, in place of the column reference. You may need an Aggr() if this means nesting aggregation functions.