Set analysis replacing Having in sql

[gouthamkk]

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

[sunny_talwar]

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)

[gouthamkk]

Hi Sunny,

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

thank you though

[sunny_talwar]

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