topic Re: Getting Maximum Date Values in Table or a chart in QlikView

Getting Maximum Date Values in Table or a chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Getting Maximum Date Values in Table or a chart

trdandamudi — Wed, 23 Nov 2016 11:10:09 GMT

May be as below:

Dimension:

Name

Expression: <--- Date Column

FirstSortedValue(Date,-Date)

Expression: <----- Profit Column

FirstSortedValue(Profit,-Date)

Re: Getting Maximum Date Values in Table or a chart

giakoum — Wed, 23 Nov 2016 11:12:13 GMT

Try FirstSortedValue

Re: Getting Maximum Date Values in Table or a chart

trdandamudi — Wed, 23 Nov 2016 11:28:00 GMT

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)

Re: Getting Maximum Date Values in Table or a chart

Anonymous — Wed, 23 Nov 2016 11:57:43 GMT

Thanks for your help

can you please explain how this expression works?

Re: Getting Maximum Date Values in Table or a chart

Anonymous — Wed, 23 Nov 2016 12:18:18 GMT

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

Like below,

Name	Date	Profit
a	11/22/2016	200

Re: Getting Maximum Date Values in Table or a chart

trdandamudi — Wed, 23 Nov 2016 12:31:09 GMT

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

=FirstSortedValue(Date,-Date,2)

=FirstSortedValue(Profit,-Date,2)

Re: Getting Maximum Date Values in Table or a chart

Anonymous — Wed, 23 Nov 2016 12:31:36 GMT

Very thanks.. i got it..

Thank for your help..

Re: Getting Maximum Date Values in Table or a chart

trdandamudi — Wed, 23 Nov 2016 12:36:32 GMT

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...

Re: Getting Maximum Date Values in Table or a chart

trdandamudi — Wed, 23 Nov 2016 12:39:34 GMT

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.