9 Replies Latest reply: Feb 8, 2016 5:12 PM by Sébastien Fatoux

# How to sum 2 fields in an IF statement

If Environ is Internal, I want the avg of Score A, If env is External, I want Avg of ScoreB, If its both, I want the avg of

scoreA and scoreB. how to write the IF statement as an expn in Straight Table.

=if(Environment='INTERNAL ',avg({\$< Enviornment={"INTERNAL"}>} ScoreA),

if(Environment='EXTERNAL ', avg({\$< Environment={'EXTERNAL'}>} ScoreB),

avg(sum(.........))) I am stuck here, any help would be appreciated

• ###### Re: How to sum 2 fields in an IF statement

As per above example, what is the value of ScoreA average.. ScoreB average and what is ScoreAandB both average..

• ###### Re: How to sum 2 fields in an IF statement

Finding it difficult to understand what is your expected output from the given sample?

• ###### Re: How to sum 2 fields in an IF statement

Maybe using Rangesum()?

avg( Rangesum(ScoreA, ScoreB))

• ###### Re: How to sum 2 fields in an IF statement

if it is Internal env, it shoud give me an avg for (30+40) which is 35. If it is external, it should avg (25+40) which is 32.5;

if it is both it should avg the averages just caculated, which means it sud avg (35+32.5) which is 33.75

• ###### Re: How to sum 2 fields in an IF statement

May be this:

=If(Environment='INTERNAL ', Avg(TOTAL <Product> {\$< Enviornment={"INTERNAL"}>} ScoreA),

If(Environment='EXTERNAL ', Avg(TOTAL <Product>{\$< Environment={'EXTERNAL'}>} ScoreB),

RangeAvg(Avg(TOTAL <Product> {\$< Enviornment={"INTERNAL"}>} ScoreA), Avg(TOTAL <Product>{\$< Environment={'EXTERNAL'}>} ScoreB))

• ###### Re: How to sum 2 fields in an IF statement

=if(Environment='INTERNAL ',avg({\$< Enviornment={"INTERNAL"}>} ScoreA),

if(Environment='EXTERNAL ', avg({\$< Environment={'EXTERNAL'}>} ScoreB),

RangeAvg( avg({\$< Enviornment={"INTERNAL"}>} ScoreA),avg({\$< Environment={'EXTERNAL'}>} ScoreB))

))

• ###### Re: How to sum 2 fields in an IF statement

Swuehl,

Thank you so much. It looks like your soln is correct. Let me plug in the changes in my qvw and I will get back to close this thread.

regards,

D

• ###### Re: How to sum 2 fields in an IF statement

swuehl, solution will work.

just another way to implement, which i read in one of the blogs works better

=pick(match(Environment,'INTERNAL','EXTERNAL','BOTH')

avg({\$< Enviornment={"INTERNAL"}>} ScoreA),

avg({\$< Environment={'EXTERNAL'}>} ScoreB),

RangeAvg( avg({\$< Enviornment={"INTERNAL"}>} ScoreA),avg({\$< Environment={'EXTERNAL'}>} ScoreB))

)

• ###### Re: How to sum 2 fields in an IF statement

Hi QlikDash,

Sunny and Swuehl give you the correct answer. They copied and completed your expression, but your expression was not correct

Their response with corrections:

=If(Environment='Internal', Avg(TOTAL <Product> {\$< Environment={'Internal'}>} ScoreA),

If(Environment='External', Avg(TOTAL <Product>{\$< Environment={'External'}>} ScoreB),

RangeAvg(Avg(TOTAL <Product> {\$< Environment={'Internal'}>} ScoreA), Avg(TOTAL <Product>{\$< Environment={'External'}>} ScoreB))

))