Getting Maximum Date Values in Table or a chart

[Anonymous]

Consider 3 fields from a table.

Name	Date	Profit
a	11/20/2016	100
a	11/22/2016	200
a	11/23/2016	230
b	11/20/2016	454
b	11/20/2016	690
b	11/22/2016	500
c	11/19/2016	123
c	11/20/2016	333
c	11/21/2016	140

I need to get only those names with maximum ( latest date) with those profit in another table or in a chart.

O/P:

Name	Date	Profit
a	11/23/2016	230
b	11/22/2016	500
c	11/21/2016	140

Please help in getting this.

Thanks

[trdandamudi]

May be as below:

Dimension:

Name

Expression:   <--- Date Column

FirstSortedValue(Date,-Date)

Expression:  <----- Profit Column

FirstSortedValue(Profit,-Date)

[giakoum]

Try FirstSortedValue

[trdandamudi · Accepted solution]

One more option is doing it in the dimension tab as below... Please see the attached:

Straight Table:

Dimenson:

Name

Dimension: <--- Date

=Aggr(FirstSortedValue(Date,-Date),Name)

Dimension: <---- Profit

=Aggr(FirstSortedValue(Profit,-Date),Name)

[Anonymous]

Thanks for your help

can you please explain how this expression works?

[Anonymous]

And what if i want to display the Second maximum date from the table

Like below,

Name	Date	Profit
a	11/22/2016	200

[trdandamudi]

If you want to get the second maximum then change the expressions as below:

=FirstSortedValue(Date,-Date,2)

=FirstSortedValue(Profit,-Date,2)

[Anonymous]

Very thanks.. i got it..

Thank for your help..

[trdandamudi]

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause.

In your case you need maximum 'Date'. So you need to add ' - ' to the date field to sort in the descending order, so that you get your maximum date.. If you don't add the ' - ' it will sort Ascending and you will get minimum date.

=FirstSortedValue(Date,-Date)

Please look on the community for few examples on FirstSortedValue... Hope this helps...

[trdandamudi]

You are welcome and I am glad it helped... If you think you got the answer then please close the thread accordingly and if not please let me know if you have any question.