aggr/rank to calculate newest 4 entries

[#1]

Hello,

I want to calculate an average for the value Ist_Pk for each [Masch.-Nr.] but it does not work.

I tried to combine Rank and Aggr function to first rank the the field "Woche" because this contains the information about the date. this means the highest 4 entries for each [Masch.-Nr.] should be Aggr. to an avg of Ist_Pk.

i tried

IF(AGGR(RANK(Woche), [Masch.-Nr.]) <= 4,avg(Ist_Pk))

Does someone have an idea how to do this?

thanks, armin

[swuehl · Accepted solution]

Try

=Avg( Aggr( IF(RANK(Woche)<=4, Ist_Pk), [Masch.-Nr.],Woche))

[jagan]

Hi,

Try like this

Avg(AGGR(IF( RANK(Woche)<= 4, Sum(Ist_Pk)) , [Masch.-Nr.], Woche) )

OR

Avg(AGGR(IF( RANK(Woche)<= 4, Only(Ist_Pk)) , [Masch.-Nr.], Woche) )

OR

Avg(AGGR(IF( RANK(Woche)<= 4, Avg(Ist_Pk)) , [Masch.-Nr.], Woche) )

Regards.

Jagan.