topic Re: Set analysis replaving Having in sql in QlikView

Set analysis replacing Having in sql

gouthamkk — Mon, 01 Aug 2016 21:38:12 GMT

HI,

i am trying to write a set analysis expression to calculate average

=avg( IF(Aggr( Count( {<Monthaname={'<=$(=AddMonths( Max(monthname),-3))'}, Monthaname ={'>=$(=AddMonths( Max(monthname),-23))'} >} Key), Key) >=5, Aggr( Miles, Key) ))

what i am trying is to get average of all the miles group by Key and this Key should be repeated more than 5 times in last 24 month excluding last 3 months

eg: For August 2016 i am trying to filter the data for August 2014 to April 2016 so i got the condition

{<Monthaname={'<=$(=AddMonths( Max(monthname),-3))'}, Monthaname ={'>=$(=AddMonths( Max(monthname),-23))'} >}

and to einclude only the "Miles" where Key is >= 5 , i placed an if condition and aggregated the set by Key.

i am getting a blank field

so i change the set expression as below

avg( IF(Aggr( Count(Key), Key) >=5, Aggr({<Monthaname={'<=$(=AddMonths( Max(monthname),-3))'}, Monthaname ={'>=$(=AddMonths( Max(monthname),-23))'} >} Miles, Key) ))

But the average calculation is wrong.

Can someone please advise

Re: Set analysis replaving Having in sql

sunny_talwar — Mon, 01 Aug 2016 22:43:57 GMT

May be like this (I am assuming your set analysis is already working for you):

Avg(Aggr(If(Count({<Monthaname = {'<=$(=AddMonths(Max(monthname), -3))>=$(=AddMonths(Max(monthname), -23))'}>} Key) >= 5, Sum(SegmentMiles)), Key)

Re: Set analysis replaving Having in sql

gouthamkk — Mon, 01 Aug 2016 23:04:02 GMT

Hi Sunny,

I tried this expression also. I am getting blank value.

thank you though

Re: Set analysis replaving Having in sql

sunny_talwar — Tue, 02 Aug 2016 01:28:39 GMT

Would you be able to provide a sample to look at?