Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL - Qlik Community

antoniodneto · ‎2018-03-27

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:

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!!

Digvijay_Singh · ‎2018-04-02

Can you share some sample data to try on this?

antoniodneto · ‎2018-04-02

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 · ‎2018-04-02

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 · ‎2018-04-03

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 · ‎2018-04-03

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 · ‎2018-04-03

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 · ‎2018-04-03

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 · ‎2018-04-04

Hi Digvijay,

Could you help me to solve?

Tks

FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

Qlik Sense