Aggregation problem in set analysis

[#1]

Hi all,

I got a problem in a set analysis.

I run the following :

avg({$<date={"$(=min(date))"}>} value) 

Which should give me the average of the value on the minimum date.

However the dollar sign function $(=min(date)) return the minimum date of the whole selected data set.

So if I produce the following table :

You can see that most of time Max average and Min average are null, simply because on the $(=min(date)) is using the minimum and the maximum of the whole selected data.

I have been trying couple of options (use column(), some aggregation ...)

Do you have an idea so the max average and min average will return the average on the Max date and min date for that code ?

Thanks you !

[sunny_talwar · Accepted solution]

Try this:

Max

FirstedSortedValue(Aggr(Avg(value), code, date), -Aggr(date, code, date))

Min

FirstedSortedValue(Aggr(Avg(value), code, date), Aggr(date, code, date))

[swuehl]

As alternative, you can try something like

=Avg( Aggr(If( date = Max(Total<code> date), value), code, date, value))

=Avg( Aggr(If( date = Min(Total<code> date), value), code, date, value))

[#4]

I works pretty well !

I need to test few case but you apparently cracked it ! Thanks