Calculation avg of each location in if statement

[apthansh]

Hi - I need help please

I have a text object where I have if statement with AVG as below..

=if(aggr(avg(IndexScore),ID) <=1,'Needs Improvement',

If(aggr(avg(IndexScore),ID)  >1  and aggr(avg(IndexScore),ID) <=3 ,'Mid Range',

If(aggr(avg(IndexScore),ID) >3  and aggr(avg(IndexScore),ID)  <=5 ,'Optimal')))

In Bar chart with ID  as dimension I have avg(IndexScore) as expression which works fine

but then I need to add the filter with category as above...but above if statement is not working...please help..

[dplr-rn]

Not sure i understand. you are facing problem in text box or the bar chart?

Another thing I assume as it is a text object you are trying to get the average as a whole and give the comment 'needs improvement' or otherwise

why do you need to use aggr in the text box? a simple avg(IndexScore) should suffice

[apthansh]

A simple avg will show the overall avg and not by each ID right

[dplr-rn]

Just for my understanding why are you trying to display average per id on text box?

Thank You

Dilip Ranjith

Sent via mobile

[apthansh]

Just trying to create this bucket called category

=if(aggr(avg(IndexScore),ID) <=1,'Needs Improvement',

If(aggr(avg(IndexScore),ID)  >1  and aggr(avg(IndexScore),ID) <=3 ,'Mid Range',

If(aggr(avg(IndexScore),ID) >3  and aggr(avg(IndexScore),ID)  <=5 ,'Optimal'))) as Ctaegory

[apthansh]

sunny‌

stalwar1‌

Any thoughts please ?

Tried below expression too but nothing shows up..

aggr(if(avg(PQIOVERALLScore) <=12,'Needs Improvemnet',

       If(Avg(PQIOVERALLScore) >12  and Avg(PQIOVERALLScore)<=18,'Mid Range',

        If(Avg(PQIOVERALLScore) >18  ,'Optimal'))),PQIFilter)

[sunny_talwar]

I am not sure I understand what you are trying to do? You want to show this in a text box object or list box? What exactly is the goal here?

[Anil_Babu_Samineni]

Where you are trying this measure, I mean selector / Object in charts? Perhaps you can try this as Measure

If(avg(IndexScore)<=1,'Needs Improvement',

If(avg(IndexScore)>1 and avg(IndexScore)<=3,'Mid Range',

If(avg(IndexScore)>3 and avg(IndexScore)<=5, 'Optional')))

[dplr-rn]

Ansh

please expand with a sample app and/or detailed scenrio as others mentioned. not clear what you are trying to achieve

[apthansh]

I created variable with the below expression and used that variable in QlikSense filter and that resolved the issue.

Than you all for sharing your thoughts.

aggr(if(avg(IndexScore) <=1,'Needs Improvement',

       If(avg(IndexScore) >1  and avg(IndexScore)<=3,'Mid Range',

        If(avg(IndexScore) >3  ,'Optimal'))),Filter)