4 Replies Latest reply: Oct 10, 2013 9:30 AM by Celambarasan Adhimulam RSS

    FirstSortedValue

    Bruce Tedder

      Hi Guys

       

      I have a straight table:

      RepName (Dimension)

      BudgetValue (Expression)

      ForecastValue (Expression)

      UpdatedDateTime (Dimension)

      Comments (Dimension)

       

      How to I create the expression for ForecastValue to return only the most recent ForecastValue based on the UpdatedDateTime.

       

      Example:

      RepName          ForecastValue     UpdateDateTime

      Bruce Tedder     R120                    09/10/2013 10:30    

      Bruce Tedder     R150                    09/10/2013 15:30

      Bruce Tedder     R90                      10/10/2013 08:45  

       

      My expression so far is:

      =FirstSortedValue(ForecastValue,-Aggr(Sum(UpdatedDateTime),RepName))

       

      What the result should be is:

      RepName          ForecastValue     UpdatedDateTime

      Bruce Tedder     R90                    10/10/2013 08:45

        • Re: FirstSortedValue
          Stefan Wühl

          If you have single records per RepName and UpdatedDateTime, you don't need the aggregation:

           

          Dimension:       RepName

           

          First Expression:      =FirstSortedValue(ForecastValue, -UpdatedDateTime)

          SecondExpression:    =Timestamp(max(UpdatedDateTime))

           

          Assuming your UpdatedDateTime is a QV timestamp with a numeric representation (dual value). If not, use a timestamp interpretation function like timestamp#() in your load script.