8 Replies Latest reply: Apr 4, 2018 8:22 AM by Antonio Domingos Neto RSS

    FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL

    Antonio Domingos Neto

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

        • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
          Digvijay Singh

          Can you share some sample data to try on this?

            • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
              Antonio Domingos Neto

              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!

                • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
                  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.

                    • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
                      Antonio Domingos Neto

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

                        • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
                          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,

                            • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
                              Antonio Domingos Neto

                              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

                              • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
                                Antonio Domingos Neto

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

                                • Re: FÓRMULA COLOUR FORMATAÇÃO CONDICIONAL
                                  Antonio Domingos Neto

                                  Hi Digvijay,

                                   

                                  Could you help me to solve?

                                   

                                  Tks