Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Calculation avg of each location in if statement

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

9 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
Creator
Creator
Author

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

dplr-rn
Partner - Master III
Partner - Master III

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

Thank You

Dilip Ranjith

Sent via mobile

apthansh
Creator
Creator
Author

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
Creator
Creator
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
dplr-rn
Partner - Master III
Partner - Master III

Ansh

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

apthansh
Creator
Creator
Author

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)