Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi Sunny,
I tried this expression also. I am getting blank value.
thank you though
Would you be able to provide a sample to look at?