topic Re: How to sum 2 fields in an IF statement in QlikView

How to sum 2 fields in an IF statement

Anonymous — Mon, 08 Feb 2016 19:41:38 GMT

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

MK_QSL — Mon, 08 Feb 2016 19:45:01 GMT

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

sunny_talwar — Mon, 08 Feb 2016 19:46:06 GMT

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

Re: How to sum 2 fields in an IF statement

swuehl — Mon, 08 Feb 2016 19:48:14 GMT

Maybe using Rangesum()?

avg( Rangesum(ScoreA, ScoreB))

Re: How to sum 2 fields in an IF statement

Anonymous — Mon, 08 Feb 2016 19:58:02 GMT

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

sunny_talwar — Mon, 08 Feb 2016 20:00:16 GMT

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

swuehl — Mon, 08 Feb 2016 20:00:58 GMT

=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

Anonymous — Mon, 08 Feb 2016 20:19:45 GMT

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,

Re: How to sum 2 fields in an IF statement

harsh44_bhatia — Mon, 08 Feb 2016 21:10:42 GMT

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

sfatoux72 — Mon, 08 Feb 2016 22:12:23 GMT

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))

))