topic Re: Find out min and max Dates and show a value only from this row in QlikView

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

Wed, 07 Dec 2011 15:58:51 GMT

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

Thanks for your help!

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

Miguel_Angel_Baeyens — Wed, 07 Dec 2011 16:11:48 GMT

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

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

Fri, 09 Dec 2011 08:36:15 GMT

Hi Miguel,

thank you so much for your answer. It solved my problem!

Regards, Martin

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

Wed, 21 Mar 2012 04:37:00 GMT

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

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

Fri, 07 Dec 2012 21:30:44 GMT

Thanks Miguel. Your solution helped me too.

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

Tue, 05 Mar 2013 14:50:27 GMT

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?

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

Fri, 17 Jun 2016 06:21:51 GMT

Hi Miguel,

Using this solution

Sum(If(Aggr(NODISTINCT Min(Date), ID) = Date, Value)) is giving me twice the value and using the second solution
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‌

stalwar1‌