topic max date in QlikView

max date

Fri, 05 Sep 2014 14:32:54 GMT

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

Re: max date

MK_QSL — Fri, 05 Sep 2014 14:39:26 GMT

Create a straight table

Dimension

[Policy No]

Expression

FirstSortedValue(Loss,-EvaluationDate)

Re: max date

alexandros17 — Fri, 05 Sep 2014 14:44:36 GMT

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

Re: max date

IAMDV — Fri, 05 Sep 2014 14:45:34 GMT

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,

Re: max date

jolivares — Fri, 05 Sep 2014 14:50:12 GMT

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