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

[#1]

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!

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

Hi Miguel,

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

Regards, Martin

[#4]

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

[#5]

Thanks Miguel. Your solution helped me too.

[#6]

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?

[#7]

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‌

stalwar1‌