topic Re: Calculation avg of each location in if statement in QlikView

Calculation avg of each location in if statement

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculation avg of each location in if statement

dplr-rn — Tue, 06 Nov 2018 21:33:03 GMT

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

Re: Calculation avg of each location in if statement

apthansh — Tue, 06 Nov 2018 21:36:02 GMT

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

Re: Calculation avg of each location in if statement

dplr-rn — Tue, 06 Nov 2018 21:38:32 GMT

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

Thank You

Dilip Ranjith

Sent via mobile

Re: Calculation avg of each location in if statement

apthansh — Tue, 06 Nov 2018 21:46:06 GMT

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

Re: Calculation avg of each location in if statement

apthansh — Tue, 06 Nov 2018 21:50:41 GMT

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

sunny_talwar — Wed, 07 Nov 2018 00:43:07 GMT

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

Anil_Babu_Samineni — Wed, 07 Nov 2018 03:25:53 GMT

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

Re: Calculation avg of each location in if statement

dplr-rn — Wed, 07 Nov 2018 14:07:16 GMT

Ansh

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

Re: Calculation avg of each location in if statement

apthansh — Wed, 07 Nov 2018 16:09:19 GMT

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)