Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Not applicable

## Find out min and max Dates and show a value only from this row

Hi everybody,

i created a table:

ID          Date               Value

1          02.04.2011          5

1          12.05.2011          2

1          23.07.2011          7

1          13.09.2011          5

1          01.02.2010          3

1          06.04.2010          9

2          16.01.2011          1

2          27.05.2011          5

2          12.03.2010          3

2          11.07.2011          9

2          09.09.2010          7

2          25.12.2011          8

3          18.11.2010          4

3          13.05.2011          6

3          29.04.2011          2

My desired Qlikview table should look like this:

ID     min Date       Value    max Date      Value

1       01.02.2010       3       13.09.2011       5

2       12.03.2010       3       25.12.2011       8

3       18.11.2010       4       13.05.2011       6

I used the the following expressions:

Min Date: =min(aggr(Date,Date,ID))

Max Date: =max(aggr(Date,Date,ID))

But how to display the correct Value in that table?

I put my Example besides...

1 Solution

Accepted Solutions
Employee

Hi,

Use the following expression for the minimum value instead:

`Sum(If(Aggr(NODISTINCT Min(Date), ID) = Date, Value))`

And likewise with the maximum value (using Max() instead of Min). I'm NODISTINCT with the Aggr() function because the minimum value may appear twice (two values for the lowest date). You're likely to get the right results using FirstSortedValue().

Hope that helps.

Miguel

EDIT: Using FirstSortedValue (don't know which one will perform faster with large data sets) the minimum value:

`Sum(Aggr(FirstSortedValue(Value, Date), ID))`

And the maximum

`Sum(Aggr(FirstSortedValue(Value, -Date), ID))`

(Note the "-" sign before Date field).

6 Replies
Employee

Hi,

Use the following expression for the minimum value instead:

`Sum(If(Aggr(NODISTINCT Min(Date), ID) = Date, Value))`

And likewise with the maximum value (using Max() instead of Min). I'm NODISTINCT with the Aggr() function because the minimum value may appear twice (two values for the lowest date). You're likely to get the right results using FirstSortedValue().

Hope that helps.

Miguel

EDIT: Using FirstSortedValue (don't know which one will perform faster with large data sets) the minimum value:

`Sum(Aggr(FirstSortedValue(Value, Date), ID))`

And the maximum

`Sum(Aggr(FirstSortedValue(Value, -Date), ID))`

(Note the "-" sign before Date field).

Not applicable
Author

Hi Miguel,

Regards, Martin

Not applicable
Author

Hi Miguel,

if i want same this output but i want to write the code in edit script.  please tell how I have to do.

thank you

Not applicable
Author

Thanks Miguel. Your solution helped me too.

Not applicable
Author

Hi,

I have a similar problem but the other way around. I want to choose the minimum value for each ID and present it's date. I've tried to rewrite your solution without any success. Any ideas how to solve this?

Not applicable
Author

Hi Miguel,

Using this solution

1. Sum(If(Aggr(NODISTINCT Min(Date), ID) = Date, Value))  is giving me twice the value and using the second solution
2. Sum(Aggr(FirstSortedValue(Value, -Date), ID))  is not giving me any result

Can you please help me what should I do. I've attache the qvf the output should be like Demo-Sheet 1 I've used both the formula's in demo sheet 2.

Thanks

Shivam

mby

Community Browser