Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
if(rank(score)<3,Avg(score),0)
score in your expression is not Capitalized ...but your field is
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.
Sorry it was typing mistake but in qlik view i used Score only.(Capital S)
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
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
Hi,
Can you attached sample file that you are working? It would be helpful in understanding the issue.
Regards,
Jagan.
Hi Jagan
Sorry the uploading is disabled in my company as we are wokring on the client data.
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.
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