Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
antoniodneto
Creator II
Creator II

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:

 

 

 

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

Labels (1)
8 Replies
Digvijay_Singh

Can you share some sample data to try on this?

antoniodneto
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Hi Digvijay,

Could you help me to solve?

Tks