Qlik Community

Qlik Brasil

Group community for Brazil users. discussion only in Portuguese.

Highlighted
antoniodneto
Contributor

FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Amigos boa tarde.

Venho compartilhar uma fórmula que fiz para formatação condicional, acredito que possa ajudar muita gente.

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.05 ,RGB(240,100,100),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.1  ,RGB(241,113,103),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.15 ,RGB(243,127,107),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.2  ,RGB(244,140,110),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.25 ,RGB(246,154,114),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.3  ,RGB(247,167,117),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.35 ,RGB(249,181,121),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.4  ,RGB(250,194,124),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.45 ,RGB(252,208,128),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.5  ,RGB(255,235,135),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.55 ,RGB(239,231,133),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.6  ,RGB(224,228,132),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.65 ,RGB(208,224,130),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.7  ,RGB(193,221,129),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.75 ,RGB(177,217,127),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.8  ,RGB(162,214,126),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.85 ,RGB(146,210,124),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.9  ,RGB(131,207,123),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.95 ,RGB(115,203,121),

if(Aggr(Rank(sum(RISCO_E_31_60)/sum(RISCO_EM_DIA_LAG1),1,1),DATA_BASE)/(NoOfColumns()-1) <=1,    RGB(100,200,120),

RGB(100,200,120)))))))))))))))))))))


Ela funciona perfeitamente colorindo os meus valores de acordo com a distribuição.

Porém preciso de uma ajuda para fazer a mesma utlizando um BEFORE dentro da fórmula desse jeito

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.05 ,RGB(240,100,100),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.1  ,RGB(241,113,103),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.15 ,RGB(243,127,107),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.2  ,RGB(244,140,110),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.25 ,RGB(246,154,114),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.3  ,RGB(247,167,117),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.35 ,RGB(249,181,121),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.4  ,RGB(250,194,124),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.45 ,RGB(252,208,128),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.5  ,RGB(255,235,135),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.55 ,RGB(239,231,133),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.6  ,RGB(224,228,132),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.65 ,RGB(208,224,130),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.7  ,RGB(193,221,129),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.75 ,RGB(177,217,127),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.8  ,RGB(162,214,126),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.85 ,RGB(146,210,124),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.9  ,RGB(131,207,123),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=0.95 ,RGB(115,203,121),

if(Aggr(Rank(sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)),1,1),DATA_BASE)/(NoOfColumns()-1) <=1,    RGB(100,200,120),

RGB(100,200,120)))))))))))))))))))))


Porém ao utilizar essa minha segunda fórmula não funciona, não sei se deve ser algo errado na construção do cálculo conforme imagem:

exemplo_colour.png

A primeira linha de 11 a 30 dias está com essa segunda fórmula com BEFORE ele aplica a mesma formatação para todos, as demais linhas estão com a primeira fórmula sem before e faz a formatação corretamente.

Será que é algum "buraco" na segunda fórmula?

Obrigado a todos!!

Tags (1)
8 Replies
Digvijay_Singh
Honored Contributor III

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Can you share some sample data to try on this?

antoniodneto
Contributor

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

There it go!

The first line "11 a 30" is with que expression that im trying to use with before function.

The others is with the expression without before that I used in another APP and works fine.

Thank you so much!

Digvijay_Singh
Honored Contributor III

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

May be this. Looks like you needed rangesum as 'before' for first column was resulting into null. Because there is nothing before first column.

antoniodneto
Contributor

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Hi Digvijay, I think is still not working. I mean for example the line "11 a 30 dias" Dec/16 = 5,2% RED and Oct/16 = 5,6% green to yellow and the line "EFICIENCIA 151-180 / 11-30"  Feb/17 = 16,9% GREEN and Jun/17 = 16,5 RED.

I think the others respect the highest as RED and lowest as GREEN.

Tks!!!

Digvijay_Singh
Honored Contributor III

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Hi,

I cannot comment on the logic of your formula as I don't know it yet. If you see the last measure I added in the attached qvf, these are the values resulted by the expression and the color is selected based on that value. As You said colors are not following your first measure values, I can see those values are not matching with the values you have in your first measure. So bottom line is, this color expression needs to be corrected.

If you can throw some light about sum(RISCO_D_15_30),before(sum({1}RISCO_A_EM_DIA)), I may try to understand, as of now I don't know what these measures means and the reasons of adding before SUM and then dividing by the no of columns.

Normally colormix function helps in this situation but the right expression needs to be created for that as well.

Thanks,

antoniodneto
Contributor

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

I understand, you have some example with colormix? I mean follow my expression

sum(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA))


How can I use the colormix to be my highest value RED and lowest GREEN?

How colormix works?


Tks

antoniodneto
Contributor

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Another way I tried but no lucky with

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.05,RGB(240,100,100),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.1,RGB(241,113,103),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.15,RGB(243,127,107),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.2,RGB(244,140,110),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.25,RGB(246,154,114),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.3,RGB(247,167,117),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.35,RGB(249,181,121),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.4,RGB(250,194,124),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.45,RGB(252,208,128),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.5,RGB(252,208,128),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.55,RGB(239,231,133),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.6,RGB(255,235,135),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.65,RGB(208,224,130),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.7,RGB(193,221,129),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.75,RGB(177,217,127),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.8,RGB(162,214,126),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.85,RGB(146,210,124),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.9,RGB(131,207,123),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=0.95,RGB(115,203,121),

if((sum(RISCO_E_31_60)/before(sum({1}RISCO_A_EM_DIA),2)) <=1,RGB(100,200,120),

RGB(100,200,120)))))))))))))))))))))

antoniodneto
Contributor

Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Hi Digvijay,

Could you help me to solve?

Tks