3 Replies Latest reply: Dec 20, 2017 7:53 AM by Gysbert Wassenaar RSS

    Using FirstSortedValue does not always work filtering on latest Date

    andries Bos

      I have a list of values (prices) where I would like to show in a chart the last value based on the latest date. Using the value fistsortedValue works only sometimes. Why is this ?

       

      In my example :

      Pricing could be set on a combination of (project + item) or (project + item + costtype) or on (project + costtype). There could be for any combination more than one price.Sometimes the costtype value is empty

       

      Source example:

       

       

      ProjectItem1DescriptionCost typecosttype descriptionlatest pricelatest date
      Project1I1Item 112.5001-01-2016
      Project1I1Item 1cost typecost description01-01-2017

       

      Outcome would be :

      First 4 columns are dimensions, the last 5 are outcome of expressions.

      Extrating the [latest price]  i've used =FirstSortedValue( (Price), - [Price.Date]   ) .

       

      Also i've tried to use Max( {< Price.Date={$(=Max(Price.Date))} >} Price )

       

      It seems that as soon as an item does have a cost type my firstsportedvalue will fail and he column

      [laatste prijs] is empty.


      How could I retrieve my latest price using the max(.price.date) based on the shown dimention? Max(price) would not work as there could have been a price drop in the history.