Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
qlikdash
Valued Contributor

How to sum 2 fields in an IF statement

sum 2 fields.PNG

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

1 Solution

Accepted Solutions
MVP
MVP

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

))

View solution in original post

9 Replies
MVP
MVP

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?

MVP
MVP

Re: How to sum 2 fields in an IF statement

Maybe using Rangesum()?

avg( Rangesum(ScoreA, ScoreB))

qlikdash
Valued Contributor

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

MVP
MVP

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

))

View solution in original post

qlikdash
Valued Contributor

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

harsh44_bhatia
Contributor

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

)

Partner
Partner

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

2016-02-08 22_56_00-Qlik Community_204726.png

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

  ))