Hierarchy wise Target vs Achievement giving Issue

[pra_kale]

Hi,

I have Two files 1 is in which I have Hierarchy and 2 nd in which have Targets and Achievements.

I have imported both this files in qlik and tried to build a Hierarchy wise Achievement %. But, my Achievement % are wrong.

What I want is, Achievement % should be of that individual person only it should not include the Targets or Achievements of below level or upper level persons.

I am attaching a sample excel files as well as application. Below given is the out put which I want in drill down as well.

Row Labels	Sum of Achievement	Sum of Target	Achievement %
AAA	2400	6000	40.0%
ABC	1400	2100	66.7%
BBB	1500	2600	57.7%
CDK	840	1200	70.0%
DEF	1200	1500	80.0%
EFG	4200	6000	70.0%
LLL	1000	1500	66.7%
RRR	1500	2100	71.4%
XXX	11000	16500	66.7%
ZZZ	1150	1800	63.9%
Grand Total	26190	41300	63.4%

Please assist to resolve the issue.

[sunny_talwar · Accepted solution]

May be this:

Pick(Match(GetCurrentField([New Group]), 'Level_1', 'Level_2', 'Level_3'),

Num(Sum(If(Level_1 = EMP_Name, Achievement)) / Sum(If(Level_1 = EMP_Name, Target))*100,0.00),

Num(Sum(If(Level_2 = EMP_Name, Achievement)) / Sum(If(Level_2 = EMP_Name, Target))*100,0.00),

Num(Sum(If(Level_3 = EMP_Name, Achievement)) / Sum(If(Level_3 = EMP_Name, Target))*100,0.00))

[jonathandienst]

I cannot get to the numbers you posted in the uploaded QVW. For example, for AAA, the achievement is 5000 and the target, leading to a % achievement of 52.08, which is what is reported for AAA. So it all appears to be working as expected.

[pra_kale]

Thanks Sunny...It is working. Great.

[pra_kale]

Hi Sunny,

The solution given by you is working perfectly. But, just one more thing want to check with you can I add any variable in this expression like if i want to exclude a current month so how i should write else i have put manually...as I tried at my end just like we used in set analysis but it is not working...can you please guide.

Num(Sum(If(Level_1 = EMP_Name, Achievement)) / Sum(If(Level_1 = EMP_Name, Target))*100,0.00),

Thanks in advance..

[sunny_talwar]

May be this?

Pick(Match(GetCurrentField([New Group]), 'Level_1', 'Level_2', 'Level_3'),

Num(Sum({<Month = {"$(='<'&Max(Month))"}>}If(Level_1 = EMP_Name, Achievement)) / Sum({<Month = {"$(='<'&Max(Month))"}>}If(Level_1 = EMP_Name, Target))*100,0.00),

Num(Sum({<Month = {"$(='<'&Max(Month))"}>}If(Level_2 = EMP_Name, Achievement)) / Sum({<Month = {"$(='<'&Max(Month))"}>}If(Level_2 = EMP_Name, Target))*100,0.00),

Num(Sum({<Month = {"$(='<'&Max(Month))"}>}If(Level_3 = EMP_Name, Achievement)) / Sum({<Month = {"$(='<'&Max(Month))"}>}If(Level_3 = EMP_Name, Target))*100,0.00))

But this might only be good for your sample where you just have Months 1, 2 and 3. You might need to use MonthYear or Date field to do this exclusion.

[pra_kale]

Hi Sunny,

You are really genius....you almost provides a solutions in a fraction of seconds it simply shows your full control over Application. Simply WOW !!!