5 Replies Latest reply: Jun 20, 2013 6:05 AM by Stefan Wühl RSS

    Selecting first available value of a sub set

      I have a field saledate.

      The use has the option to choose the too and from date of all the data.

      I want a table that shows the sum(value) on the last available saledate, from the too
      date that the user input.

      I was looking at using set analysis: for saledate<toodate get saledate = max(saledate).

      This date is different each value on my dimensions

       

      This is the data

       

       

       

       





































       

      Sales
        person


       

       

      Saledate


       

       

      Value


       

       

       


       

       

      John


       

       
      1. 21.2.2013
       

       

      35


       

       

       


       

       

      Andy


       

       
      1. 12.1.2013
       

       

      10


       

       

       


       

       

      Andy


       

       
      1. 12.2.2013
       

       

      15


       

       

       


       

       

      Mike


       

       
      1. 25.05.2013
       

       

      30


       

       

       


       

       

      John


       

       
      1. 10.03.2013

       

      15


       

       

       


       

       

       

       

      If the user inputs the too date as 12.04.2013 then Mike should be excluded and only Johns sale on 10.03.2013 should be included.

      And both  Andy’s sales should be included

       

      The table should show:

       

       

       

       

       
















       

      Sales
        Person


       

       

      Value
        on too date


       

       

       


       

       

      Andy


       

       

      25


       

       

       


       

       

      John


       

       

      15


       

       

       


       

       

       

      I have tried various set analysis solutions but I’m not sure if I am getting the syntax right.