topic Re: positive growth commission amount finding problem in QlikView

positive growth commission amount finding problem

gzboy008 — Tue, 30 Jul 2013 12:54:57 GMT

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~

Re: positive growth commission amount finding problem

gzboy008 — Wed, 31 Jul 2013 03:42:26 GMT

Excuse me... Does anyone have an idea?

Thanks.

Re: positive growth commission amount finding problem

Kushal_Chawda — Wed, 31 Jul 2013 04:17:33 GMT

Hi,

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

Regards,

Kush

Re: positive growth commission amount finding problem

gzboy008 — Wed, 31 Jul 2013 06:06:47 GMT

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

Re: positive growth commission amount finding problem

Kushal_Chawda — Wed, 31 Jul 2013 06:22:30 GMT

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

Re: positive growth commission amount finding problem

gzboy008 — Wed, 31 Jul 2013 10:47:53 GMT

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~

Re: positive growth commission amount finding problem

Kushal_Chawda — Wed, 31 Jul 2013 11:09:07 GMT

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

Re: positive growth commission amount finding problem

gzboy008 — Wed, 31 Jul 2013 11:53:40 GMT

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~

Re: positive growth commission amount finding problem

Kushal_Chawda — Wed, 31 Jul 2013 12:04:43 GMT

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

Re: positive growth commission amount finding problem

Kushal_Chawda — Wed, 31 Jul 2013 12:45:25 GMT

Finally I got your requirement.

See the attached application

Re: positive growth commission amount finding problem

gzboy008 — Thu, 01 Aug 2013 01:08:26 GMT

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