max date

[#1]

HI, I am new to qlikview. I have a scenario like this.Please let me know hoe to resolve?

Policy No	Pol Eff Date	EvaluationDate	Loss
1	1/1/2010	1/31/2011	$45,643
1	1/1/2010	3/31/2011	$45,644
1	1/1/2010	4/30/2012	$45,645
1	1/1/2010	5/30/2012	$45,646
2	1/1/2011	1/31/2011	$55,645
3	2/1/2012	3/31/2011	$23
3	2/1/2012	4/30/2012	$46
3	2/1/2012	5/31/2012	$47
4	2/1/2012	6/30/2014	$647
4	2/1/2012	7/31/2014	$648

The scenario is

I have to pull the loss for each policy/eff date for the maximum evaluation date.

ie for eg: Policy 1, i have to show $45646, because this is the loss amount for maximum evaluation date ie 5/30/2012

[MK_QSL]

Create a straight table

Dimension

[Policy No]

Expression

FirstSortedValue(Loss,-EvaluationDate)

[alexandros17]

In the script:

AAA:

LOAD * Inline [

PolicyNo, EffDate, EvaluationDate, Loss

1, 1/1/2010, 1/31/2011, 45,643

1, 1/1/2010, 3/31/2011, 45,644

1, 1/1/2010, 4/30/2012, 45,645

1, 1/1/2010, 5/30/2012, 45,646

2, 1/1/2011, 1/31/2011, 55,645

3, 2/1/2012, 3/31/2011, 23

3, 2/1/2012, 4/30/2012, 46

3, 2/1/2012, 5/31/2012, 47

4, 2/1/2012, 6/30/2014, 647

4, 2/1/2012, 7/31/2014, 648];

BBB:

NoConcatenate

LOAD * Resident AAA;

Left Join

LOAD PolicyNo, EffDate, Date(Max(EvaluationDate)) as maximo Resident AAA Group By PolicyNo, EffDate;

DROP Table AAA;

in this way you have a new field that you can use in your object to take Max values.

Let me know

[IAMDV]

Hi Rakesh,

This is perfect scenario to use FirstSortedValue() function. I've made a video tutorial on this top. Please check the following link(s) on my blog. It's really powerful to use and very useful when you want to get max or min value based on a specific field. One more advantage - FirstSortedValue() supports SET Expression (SET Analysis).

http://qlikshare.com/392/

http://qlikshare.com/398/

I hope this helps!

Cheers,

DV

[jolivares]

If I understand, you need the maximum EvaluationDate for each Policy No.

If so... Make a table with Policy No, Max Eval Date, Loss.

Express. for Max Eval Date = Date(Max(Aggr(Max(F3),F1,F3)))

And for Loss FirstSortedValue(F4,-Aggr(Max(F3),F1,F3))