Min function in a table

[#1]

Hi

I have two columns in a table,

1. Fund

2. Benchmark

Funds are categorized in 3 buckets

-- Parent

-- Child

-- Individual

A fund can have either no benchmark, one benchmark or many benchmarks.

Each benchmark has a benchmark rank associated with it which goes from 1 to 8

Not every fund have more benchmark so there are cases where there is only fund but no benchmark record associated with it.

In the table I need to show only those funds that are categorized as "Parent"

Corresponding to each fund, in the benchmark column, I need to find the benchmark which has the minimum rank and display the corresponding name of that benchmark.

The exercise seems very simple, however using the following expression in set analysis I am getting null values in the benchmark column.

Set analysis:

only({1< parent_clone_type={'Parent'},

              benchmark_rank = {'$(=min(benchmark_rank)'}

       >} 

benchmark_name_short)

However, upon hard coding benchmark rank = 1, it displays the names of the benchmark as expected. Unfortunately I can't hardcode benchmark rank because for certain funds the minimum benchmark rank could be 2 or 3 for example so hard coding 1 doesn't work for this problem.

Set analysis:

only({1< parent_clone_type={'Parent'},

              benchmark_rank={'1'}

       >} 

benchmark_name_short)

Any idea what's the error in my code?

Thanks

[swuehl]

Set analysis is evaluated once per chart, so the min rank would not be determined based on the dimension value.

Try FirstSortedValue instead:

FirstSortedValue( DISTINCT

{1< parent_clone_type={'Parent'} >} 

benchmark_name_short,

benchmark_rank

)

[#3]

Thanks Swuehl

Finally i need to assign the out put of minimum rank (1 or 2 or whatever the minimum rank is) to another attribute.

I am using the following set analysis but somehow benchmark_rank is not being assigned the minimum value retrieved by the first sorted value.

Sum({1< parent_clone_type={'Parent'},

       benchmark_rank={'$(=FirstSortedValue( DISTINCT {1< parent_clone_type={'Parent'} >}       benchmark_rank, benchmark_rank))'}

  >}

benchmark_contrib_mktval_weight)

[swuehl]

Well, set analysis is still evaluated once per chart and if you expect the benchmark_rank to be set depending on the chart's dimension, this still will not work.

Maybe try another

FirstSortedValue( DISTINCT

({1< parent_clone_type={'Parent'}  >}

benchmark_contrib_mktval_weight,

benchmark_rank

)

Or try to explain how your model looks like, it for sure shows more than the initally mentioned two columns.