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]

Excuse me... Does anyone have an idea?

Thanks.

[Kushal_Chawda]

Hi,

See the attched application and let me know if this helps.

Regards,

Kush

[gzboy008]

Hello Kush, thanks for your reply.

I have posted my expected result. You solution seems a little appart from my expected result.But thanks for your effort anyway

[Kushal_Chawda]

Hi,

If you carefully see the output of my application I am getting the same result as you have highlighted.

In your case it's just a change of way of represtation

[gzboy008]

Excuse me.Maybe I really miss something.

I just need two records.

Because my dimensiton will be agency_code. agency_history_year & target_commission_amount is in expression.

Thank you~

[Kushal_Chawda]

Hi

I am not getting actually, as for agency_code 001 there is 3 years 2010,2012,2013 and for agency_code 002 as well 3 years. So how will you get only 2 records.

Please find the attched one .. is it what you are looking for

[gzboy008]

Hello Kush,

My result is

Your result is

They are different. And once more, I need to use only agency_code as dimension. Not Agency_code + Agency_history_year.

Thanks~

[Kushal_Chawda]

can you tell me why you are not getting the record for year 2013 for agency code 001?

[Kushal_Chawda]

Finally I got your requirement.

See the attached application