topic Re: FirstSortedValue in QlikView

FirstSortedValue

Thu, 10 Oct 2013 12:50:53 GMT

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

swuehl — Thu, 10 Oct 2013 12:59:33 GMT

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.

Re: FirstSortedValue

Thu, 10 Oct 2013 13:16:00 GMT

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,

Re: FirstSortedValue

swuehl — Thu, 10 Oct 2013 13:19:51 GMT

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

Re: FirstSortedValue

CELAMBARASAN — Thu, 10 Oct 2013 13:30:05 GMT

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