topic Re: Setting conditions for an average field in QlikView

Setting conditions for an average field

Wed, 10 Sep 2014 14:44:17 GMT

Hi guys,

I'm trying to do an average of a field if a condition is met .

I've tried this but its not working any ideas where im going wrong !

This is the original statement which works fine :

Avg($(vFactor1))-1/3

I wanted to include something to only do the average where Wave =vWaveCurr

I tried this and it was not working 😕

Avg($(vFactor1) and Wave = vWaveCurr )-1/3

Re: Setting conditions for an average field

israrkhan — Wed, 10 Sep 2014 14:49:19 GMT

try like this..

Avg({<Wave = {"=$(vWaveCurr) }" >} $(vFactor1)-1/3) or

if(Wave = $(vWaveCurr), AVG($(vFactor1)-1/3)), null )

Re: Setting conditions for an average field

jonathandienst — Wed, 10 Sep 2014 14:51:09 GMT

=If(Wave = vWaveCurr, Avg($(vFactor1)) - 1/3)

HTH

Jonathan

Re: Setting conditions for an average field

Wed, 10 Sep 2014 15:03:52 GMT

Hi Guys I tried both for Isrars one it was coming up with error in set modifier ad hoc element '.' or ')' expected do I need to add something to it ?

Re: Setting conditions for an average field

jonathandienst — Wed, 10 Sep 2014 15:07:17 GMT

To debug further, you will need to explain what is in vFactor1 and vWaveCurrent.

Re: Setting conditions for an average field

Wed, 10 Sep 2014 15:14:23 GMT

Thanks vFactor1 is one of the fields we are using just contains ranking scores, vWaveCurrent is set in initial page to the latest wave of data/

Re: Setting conditions for an average field

israrkhan — Wed, 10 Sep 2014 16:13:08 GMT

Dear Sarah

sorry, i could not understand, can you please share sample document,

i am sure i can help you....

Re: Setting conditions for an average field

Thu, 11 Sep 2014 08:13:06 GMT

Hi Guys, this is kinda what Im trying to do I wanted a separate expression for each wave vWaveCurr and vWavePrev like in the text box but keeps falling down not sure why

Re: Re: Setting conditions for an average field

israrkhan — Thu, 11 Sep 2014 08:31:35 GMT

see the expression are working, and you can modify accordingly..

Re: Re: Setting conditions for an average field

nam — Thu, 11 Sep 2014 08:45:07 GMT

Firstly your variables are incorrect.

vFactor1=Heading 1

Heading 1 has values 1,2 3 so the above will return you null so will not work

so firstly change vFactor1=avg(Heading).

anyway just change the expression in the textbox to :

=avg({<Wave={$(vWaveCurr}>} Heading1)

Re: Re: Setting conditions for an average field

Thu, 11 Sep 2014 15:34:39 GMT

Thanks this really helps me is it possibility to have vFactor1 instead of heading 1 as the heading names change quite a lot

the trouble is I cant change the factor definition as it feeds into other sources

Thanks

Sarah