Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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,
You are using more than one dimension, right? Use one dimension and two expressions, as given above.
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