Hello Qlik community I have a question:
First I have fields like plan, sales, employees and column parent where the employees that have same supervisor .
The data I have is I have employee A and employee B and the employee C is their supervisor.
Employee A made =5 sales and his plan =2 and employee B made =3 sales and his plan was =2.
Now based on the data I provided the first employee made 250% and the second made 150% based on sales/plan.
But since we have upper limit of 200% the this end with employee A= 200% and employee B =150%
This is the syntax that I used to calculate this precentages and end up with those number(200%,150%):
If (num (sum({<TABLE= {'confirm'}, product={'AA','BB'}>}CAL)-[PLAN])/[PLAN]*100+100<0,0
If (num (sum({<TABLE= {'confirm'}, product={'AA','BB'}>}CAL)-[PLAN])/[PLAN]*100+100> kap( this is variablewhich has value 200),kap
(sum({<TABLE = {'confirm'}, product= {'AA','BB'}>}CAL)- [PLAN]) / [PLAN]*100+100
Now here comes my problem I also have to calculate average for supervisor (employee C)--> which in this case would be 175%, but how to I achieve that??
I tried with this syntax:
if( function='UP', -->(which means parent)
if( ( sum(total <parent> aggr(sum({<TABLE= {'confirm'}, product= {'AA','BB'}>}CAL), parent)) - sum(total <parent> aggr(sum([PLAN]), parent))) / sum(total <parent> aggr(sum([PLAN]), parent))*100+100 < 0, 0,
if( ( sum(total <parent> aggr(sum({<TABLE= {'confirm'}, product= {'AA','BB'}>}CAL), parent)) - sum(total <parent> aggr(sum([PLAN]), parent))) / sum(total <parent> aggr(sum([PLAN]), parent))*100+100>kap,kap
( sum(total <parent> aggr(sum({<TABLE= {'confirm'}, product= {'AA','BB'}>}CAL), parent)) - sum(total <parent> aggr(sum([PLAN]), parent))) / sum(total <parent> aggr(sum([PLAN]), parent))*100+100
But the problem with this is that for supervisor I get value=200% which is incorrect.
Than I also tried with creating variables
- totalplan -->sum(PLAN)
-totalst --> sum({<TABLE={'confirm'},product={'AA','BB'}>}CAL)
if( function='UP', -->(which means parent)
AVG(
aggr(
if( (sum(total <parent> aggr($[totalst], parent)) -sum(<parent> $[totalplan]))/sum(total <parent> $[totalplan])*100+100<0,0,
if((sum(total <parent> aggr($[totalst], parent)) -sum(<parent> $[totalplan]))/sum(<parent>$[totalplan])*100+100> kap,kap
(sum(total <parent> aggr($[totalplan], parent))-sum(<parent>$[totalplan]))/sum(<parent>$[totalplan])*100+100
)),parent
)
)
But in this case the result is =-
