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


      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.



      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:



      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.