topic add value, showing only one in QlikView

add value, showing only one

Wed, 06 Oct 2010 20:27:23 GMT

I have a table with: names, values and dates:

A____ 200 ____23/02/2007

A____ 0 ______30/12/2010

A____ 67 _____10/07/2009

A____ 200 ____22/09/2008

B____ 170 ____08/05/2008

B____ 80 _____14/10/2010

B____ 10 _____19/04/2009

I want to show those with the largest value with its corresponding date. If by chance there are equal values, want to show what date has the largest (latest).

in this case would be:

A____ 200 ____22/09/2008

B____ 170 ____08/05/2008

because they have the most value. In the case of A, there are repeated values, making it appear the most recent

can someone help me, please?

add value, showing only one

Wed, 06 Oct 2010 20:32:20 GMT

Use Name as your dimension. Then Max(Value) would get Expression 1. For Expression 2, Max(If(Value=Max(Value), Date)) or Max(If(Value = Column(1), Date)). I think either of those should work.

add value, showing only one

Wed, 06 Oct 2010 20:36:58 GMT

excuse me ... I need to show only the latest. The value must be different from 0, regardless of whether the highest or lowest

add value, showing only one

Wed, 06 Oct 2010 20:40:39 GMT

Have a look to the FIRSTSORTEDVALUE ; maybe it will help you

JJJ

add value, showing only one

Wed, 06 Oct 2010 20:52:23 GMT

give me an example

add value, showing only one

Wed, 06 Oct 2010 22:14:57 GMT

I kind of missed the nested aggregates in my first suggestion. This expression works fine:

Date(Max(If(Value=Aggr(Max(Value), Name), Date)))

I don't get the must not be zero comment. Do you have negatives? If not, how could zero ever be the max?

This expression will show the Date of the greatest Value per Name. If there are multiple Dates tied for highest Value, it will show the greatest Date.