8 Replies Latest reply: Apr 11, 2018 11:04 AM by Antonio Domingos RSS

    Conditional Format

    Antonio Domingos

      Hi Guys,

       

      I have a table that I want to colour my valus by highers in red, medium in yellow and lowers in green.

       

      Each row has you specific expression to calc.

       

      Any ideas how Can I do that?

       

      Follow my app attached and my desirred view in an Excel picture.

       

       

      exemplo_colour2.png

      Tks a lot!!!

        • Re: Conditional Format
          Pasquale Cosimato

          In the measure or dimension of your table, you ca find a property Background color expression or text color expression.

          There you can put your formula:

           

          if ( 'formula' > 0 and 'formula' < 10, 'red',

          if ( 'formula' > 10 and 'formula' < 50, 'orange',

          'green' ))

           

           

          You can update the script with your formula inside.

           

          Hoping this helps

            • Re: Conditional Format
              Antonio Domingos

              Not working!

               

              Follow my formula

              if (SUM(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)) > 0 and SUM(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)) < 0.1, 'red',

              if ( SUM(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)) > 0.1 and SUM(RISCO_D_15_30)/before(sum({1}RISCO_A_EM_DIA)) < 0.5, 'orange',

              'green' ))

               

              All values in red!

                • Re: Conditional Format
                  Pasquale Cosimato

                  Hi Antonio, I test the formula and look fine.

                  try just to change the number format of your rows. You can see the percentage, but the real number is between 0 and 1 for this reason is always red.

                  If you try to custom format in your data as #,##0.0000 you can see the real values of your data.

                   

                  In the formula you need to specify a bit more your range :

                  if ((SUM(RISCO_D_15_30))/(before(sum({1}RISCO_A_EM_DIA))) > 0 and (SUM(RISCO_D_15_30))/(before(sum({1}RISCO_A_EM_DIA))) < 0.0380, 'red',

                  if (( SUM(RISCO_D_15_30))/(before(sum({1}RISCO_A_EM_DIA))) > 0.0380 and (SUM(RISCO_D_15_30))/(before(sum({1}RISCO_A_EM_DIA))) < 0.045, 'orange',

                  'green' ))

                   

                  Let me know if it works now.

              • Re: Conditional Format
                Thiago Justen Teixeira

                Antonio,

                 

                Como sugerido na comunidade Brasil, acho meio complicado deixar dinâmico o controle de cores dado a sua estrutura da pivot table. Entretanto, fiz alguns testes com o ColorMix2. Veja:

                 

                Usando esta expressão:

                ColorMix2(hrank(total column(1))/NoOfColumns(TOTAL),rgb(234, 32, 39),rgb(46, 204, 113),rgb(255, 242, 0))


                A paleta de cores segue:

                Capturar.PNG