topic Conditional within rank/agg in QlikView

Conditional within rank/agg

danielact — Wed, 24 Aug 2011 13:43:13 GMT

I have a lot of data on insurance policies. I have a table showing the top 10 policies by premium. Here's my function for the dimension:

=if(aggr(rank(Premium), Year)<11, Policy_Nbr)

I was asked to only show recent policies - let's say in the last year. I have a field which shows when the policy was written, which is called pol_date. Any idea on how I can limit that function only to the last year? I created a variable called vRecentDate which is set to =max(pol_date) - that way when filters are applied, the value of the recet date will update to the data being looked at. But how do I work it into the original function?

Conditional within rank/agg

sridhar240784 — Wed, 24 Aug 2011 14:00:13 GMT

Wil this help you?

=if(aggr(rank(Sum({<Year = {"$(=Year(Max(pol_date))-1)"}>} Premium)), Year)<11, Policy_Nbr)

If this is not helping you, pl post your sample data.

-Sridhar

Re: Conditional within rank/agg

danielact — Wed, 24 Aug 2011 15:50:03 GMT

No, that doesn't help. I realized an error in my original script and corrected it (I was summing ALL premium instead of taking only the one year).

I'm attaching an example of it. I want to be able to limit the year to only the latest one - take the max of the dates, and go back one year from it.

Re: Conditional within rank/agg

sridhar240784 — Thu, 25 Aug 2011 10:02:32 GMT

Hi,

Check out the attached application.

Your Expression should be like below.

aggr(rank(Sum({<UW_Year = {"$(=Max(UW_Year))"}>} Premium)), Contract_Year)

Hope this helps you.

-Sridhar