3 Replies Latest reply: Feb 19, 2016 6:15 PM by Stefan Wühl RSS

    Min function in a table

    Hemang Dave

      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)

       

      benchmark (Null).PNG

      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)

       

      benchmark (NON-Null).PNG

       

      Any idea what's the error in my code?

       

      Thanks

        • Re: Min function in a table
          Stefan Wühl

          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

          )

            • Re: Min function in a table
              Hemang Dave

              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)

                • Re: Min function in a table
                  Stefan Wühl

                  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.