topic Convert the if statement into a set analysis expression in QlikView

Convert the if statement into a set analysis expression

divya_anand — Tue, 30 May 2017 05:32:33 GMT

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.

Re: Convert the if statement into a set analysis expression

divya_anand — Tue, 30 May 2017 06:10:22 GMT

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?

Re: Convert the if statement into a set analysis expression

avinashelite — Tue, 30 May 2017 06:31:29 GMT

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

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

Re: Convert the if statement into a set analysis expression

jonathandienst — Tue, 30 May 2017 06:33:26 GMT

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.