How to show the year in which the minimum monthly value exists

[#1]

Hi,

I'm getting a mental block on this, it seems quite a simple problem but I can't get my head around it!

I have monthly totals for a number of years:

Month,Year,Value

1,2000,12

2,2000,14

...

12,2000,19

1,2001,43

2,2001,34

3,2001,21

..

1,2002,23

..

12,2002,34

I would like to display a table that contains

Month   Min(Value)   Year

So that I have a list of months from January to December, with the lowest Value for that month and the year in which that monthly value was e.g.

Month   Min Value    Year

Jan        12        2000

Feb         8        1987

Mar        15        2010

Apr        65        1993

...

Dec        32        1972

I've tried various combinations of dimensions and expressions, but not getting very far. Any ideas/pointers in the right direction would be appreciated.

Adrian

[swuehl · Accepted solution]

Try Month as dimension and two expressions:

=min(Value)

=FirstSortedValue(Year, Value)

Hope this helps,

Stefan

[#3]

Thanks for the quick reply! Ended up having to use FirstSortedValue(distinct yr, val) and that worked a treat.