topic Re: Taking average by if else? in QlikView

Taking average by if else?

Fri, 01 Mar 2013 05:57:10 GMT

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

Re: Taking average by if else?

phaneendra_kunc — Fri, 01 Mar 2013 06:02:46 GMT

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

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

Re: Taking average by if else?

jagan — Fri, 01 Mar 2013 06:03:58 GMT

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.

Re: Taking average by if else?

Fri, 01 Mar 2013 06:04:36 GMT

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

Re: Taking average by if else?

Fri, 01 Mar 2013 06:09:18 GMT

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

Re: Taking average by if else?

kaushiknsolanki — Fri, 01 Mar 2013 06:09:18 GMT

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

Re: Taking average by if else?

jagan — Fri, 01 Mar 2013 06:12:11 GMT

Hi,

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

Regards,

Jagan.

Re: Taking average by if else?

Fri, 01 Mar 2013 06:32:00 GMT

Hi Jagan

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

Re: Taking average by if else?

jagan — Fri, 01 Mar 2013 06:46:09 GMT

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.

Re: Taking average by if else?

Fri, 01 Mar 2013 07:27:07 GMT

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