Taking average by if else?

[#1]

I have 3 columns Named as Area,Their performance score and its rank across all areas.

Sample:

Area Score Rank

Chicago 80 1

Bay 60 2

Ohio 50 3

LA 40 4

Now I want to take the average scores of all those areas  when rank is less than 3 and when rank is greater than 2. How to do it ?
If it has to be done by if else please tell me the syntax

I tried if(rank(score)<3,Avg(score),0)

It says bad field name Score

[phaneendra_kunc]

if(rank(score)<3,Avg(score),0)

score in your expression is not Capitalized ...but your field is

[jagan]

Hi Manu,

Try this expression

For Rank is <2:

=Avg({<Rank={'<3'}>} Score)

For Rank > 2:

=Avg({<Rank={'>2'}>} Score)

Hope this helps you.

Regards,

Jagan.

[#4]

Sorry it was typing mistake but in qlik view i used Score only.(Capital S)

[#5]

Same issue. Bad field name. Your formula might be right Jagan and I think there is a small mistake at my end.

I would like to tell you that Score is a just a label and in expression I have the whole calculation to calculate it. If you can think of possible reasons for Bad field name plz enlighten me with it

[kaushiknsolanki]

HI,

     I would bit change expression given by Jagan.

     =Avg({<Rank = {"<3>=2"}Score)

     This will give the avg of score only where the rank is greater than 3 and lesser than 2.

Regards,

Kaushik Solanki    

[jagan]

Hi,

Can you attached sample file that you are working? It would be helpful in understanding the issue.

Regards,

Jagan.

[#8]

Hi Jagan

Sorry the uploading is disabled in my company as we are wokring on the client data.

[jagan]

Hi Manu,

How you calculating the rank, it is a static/dynamic, the expressions given exactly works as expected, can you post some sample data.

Regards,

Jagan.

[#10]

So data is something like that

To calculate the score I have 10 metrics. We give the score for each metric from 0 to 10 and add all those 10 metrics to get the score for an area.

So while defining score there is entire calculations to get score for each metric and then adding them up.

Label: Score

Exp: Sum of each metric score for each area

Rank: Rank(Score): it gave me the rank of all areas.

The data is for 3 months so Score and Rank changes according to the month selection. hence they are dynamic