FirstSortedValue

[#1]

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

[swuehl]

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.

[#3]

Thanks

I used your first expression and I get the following:

What need to see it only the most recent record and it should be the top one?

What should I be doing with the second expression?

The ForecastUpdated field is derived in the load script as follows:

Date(Date(YN_ADD_DATE,'YYYY/MM/DD') + Time(Time#(YN_ADD_TIME, 'hhmmss'),'hh:mm'),'DD/MM/YYYY HH:MM') as ForecastUpdated,

[swuehl]

You are using more than one dimension, right? Use one dimension and two expressions, as given above.

[CELAMBARASAN]

As swuehl suggested You should use

RepName (Dimension) = RepName

BudgetValue (Expression) = Your Expression

ForecastValue (Expression) = FirstSortedValue(ForecastValue, -UpdatedDateTime)

UpdatedDateTime (Expression) = Timestamp(max(UpdatedDateTime))

Comments (Dimension) = Comments