topic FirstSortedValue with Aggr in QlikView

FirstSortedValue with Aggr

ilanbaruch — Thu, 03 May 2018 08:37:23 GMT

hi all,

i have an aggr expresion which retrives Status dates,

=DATE(aggr(status_date,order),'DD/MM/YYYY')

i want to retrive the min date value

tried it with:

FirstSortedValue(status_date, aggr(status_date,order))

any ideas?

advanced thanks

Re: FirstSortedValue with Aggr

YoussefBelloum — Thu, 03 May 2018 08:43:54 GMT

Hi,

would you be able to share with us more details on this ?

what you have so far and what is the expected output ?

Re: FirstSortedValue with Aggr

ilanbaruch — Thu, 03 May 2018 08:55:11 GMT

order	date	quantity	Status
1-9660100	22/04/2018	1	75
1-9660100	23/04/2018	1	75
1-9660100	24/04/2018	1	75
1-9660100	25/04/2018	1	75

i need to retrive only one row whith the min/max date

Re: FirstSortedValue with Aggr

Miguel_Angel_Baeyens — Thu, 03 May 2018 08:56:37 GMT

Did you try?

FirstSortedValue(order, -date)

With the "-" before the date to get the highest value (most recent date) or without it for the opposite (oldest date)

Re: FirstSortedValue with Aggr

tresB — Thu, 03 May 2018 08:58:53 GMT

Straight table:

Dimension: Order

Exp1: Date(Max(Date))

Exp2: FirstSortedValue(Quantity, -Date)

Exp3: FirstSortedValue(Status, -Date)

For Min you could adjust.

Re: FirstSortedValue with Aggr

sunny_talwar — Thu, 03 May 2018 09:04:33 GMT

If the Quantity and Status are always the same across all dates, then you can also use Only(Quantity) and Only(Status), but this might be only true in the sample, but just to throw out an option