Expression reuse with differents filters

Thu, 19 Jan 2012 18:02:39 GMT

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

Expression reuse with differents filters

Thu, 19 Jan 2012 19:50:59 GMT

Hi Dariomalta,

If it's possible to write your expression in set analysis instead of all those complex 'IF' conditions you can create variables with different filters and reuse the same expression by changing variables

Hope that helps

Meher

topic Expression reuse with differents filters in QlikView

Expression reuse with differents filters

Expression reuse with differents filters