positive growth commission amount finding problem

[gzboy008]

Dear guys,

        I've been acting as a Qlikview developer for more than four months. Unfortunately, I'm now in a deep puzzle for retrieving positive growth problem these days. Does somebody have any idea? Thanks.

testing data:

expect result:

Here's the logic.

sum up commission_amount by agency_code & gency_history_year. Using this amount deduct previous year's amount to find out positive amount. Retrieve the most recently year and the deduct amount.

For example, agency_code 001. 2013's commission amount - 2012's commission amount = 30 - 90 = 60 < 0. Because it's negative amount, so I need to use 2012's amount - 2010's amount = 90 - 60 = 30 >0. So 2012(year) and deducted amount(30) is what I want.

Agency_code 002 is the same story as Agency_code 001.

I prefer something like set analysis solution.

Thanks so much~

[gzboy008]

Hi Kush, seems the result's fine. I'll study your program and try to apply in my job. Thanks for your time, really!