Hello everyone,
I'm developing a BSC document with Qlikview.
I've made a lot of buttons, that are the objectives, and we you click on them, the objective is filtered and the kpi is shown.
No problems till there.
The problem is that the borders of the button, must be colored based on the Objective formula.
All the objectives has the same formula. And this formula is on the variable vPercMetaMapa and its content:
if(
if( count (DISTINCT MedidaBSC)>1,
avg( aggr(
/********** INICIO expressao de resultado do grafico abaixo **********/
if(lower(POLARIDADE)='maior melhor',
if(1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))),
if(1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC))))
)
/********** FIM expressao de resultado do grafico abaixo **********/
, MesAnoBSC,DescIndicador,MedidaBSC,POLARIDADE))
,
if(lower(POLARIDADE)='maior melhor',
if(1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))),
if(1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC))))
) )
>1.5, 1.5,
if( count (DISTINCT MedidaBSC)>1,
avg( aggr(
/********** INICIO expressao de resultado do grafico abaixo **********/
if(lower(POLARIDADE)='maior melhor',
if(1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))),
if(1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC))))
)
/********** FIM expressao de resultado do grafico abaixo **********/
, MesAnoBSC,DescIndicador,MedidaBSC,POLARIDADE))
,
if(lower(POLARIDADE)='maior melhor',
if(1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1+((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))),
if(1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC)))<0,0,1-((sum(RealizadoBSC)-sum(MetaBSC))/if(sum(MetaBSC)=0,1,sum(MetaBSC))))
) )
)
This formula has no filter. Now, for each button I need to use this same formula, but using the objective filter.
I've tried just like the image attached is showing, but it did not work.
Can anyone help me with this.
Or anyone have done something like this ? There is another way to do this ?
Any help is welcome
Thanks all,
Dario Malta
