Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
apthansh
Contributor

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

Re: Calculation avg of each location in if statement

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
Contributor

Re: Calculation avg of each location in if statement

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

Partner
Partner

Re: Calculation avg of each location in if statement

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

Thank You

Dilip Ranjith

Sent via mobile

apthansh
Contributor

Re: Calculation avg of each location in if statement

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
Contributor

Re: Calculation avg of each location in if statement

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)

Re: Calculation avg of each location in if statement

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?

Re: Calculation avg of each location in if statement

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Calculation avg of each location in if statement

Ansh

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

apthansh
Contributor

Re: Calculation avg of each location in if statement

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)