3 Replies Latest reply: Jul 18, 2016 11:09 AM by Miguel Braga RSS

    Conditional Formatting table with a range

    Emma Carcas

      Hi there,

       

      I have a table with a % Net recovery column in. I want to be able to colour this column three different colours based on the value.

       

      0-69% Red

      70-99% Orange

      100%+ Green

       

      I have done this successfully for <70% being Red otherwise everything else is Green as below and all works (the expression is further complicated by the complex data I have!):

       

      if( num(

      Pick(CompIndex

      ,

      ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

        +

            SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

        )/

            sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

        )

        ,

         ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

        +

            SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

        )/

            sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

        ))

        ,'##0%') <0.70,RgbRed,RgbDarkGreen)

       

       

      Would anyone be able to help me with adding the additional logic for the mid range? Is it possible in Qlik Sense?

       

      Thank you,

      Emma

        • Re: Conditional Formatting table with a range
          Miguel Braga

          I believe this is what you require:

           

          if( num(

          Pick(CompIndex

          ,

          ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

            +

                SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

            )/

                sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

            )

            ,

             ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

            +

                SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

            )/

                sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

            ))

            ,'##0%') <0.70,

           

          if( num(

          Pick(CompIndex

          ,

          ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

            +

                SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

            )/

                sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

            )

            ,

             ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

            +

                SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

            )/

                sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

            ))

            ,'##0%') >= 0.70 and

          num(

          Pick(CompIndex

          ,

          ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

            +

                SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

            )/

                sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

            )

            ,

             ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

            +

                SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

            )/

                sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

            ))

            ,'##0%') < 1,

          RgbRed,RgbDarkGreen

          )

          )

           

          Hope this helps

           

          Regards,

          MB

           

          PS: (WARNING) didn't tested it, may have problems with parenthesis

            • Re: Conditional Formatting table with a range
              Emma Carcas

              Thank you Miguel. I am not sure I see where the Orange (RgbOrange in my case) will come in?

               

              I should have added to my original post that these are custom colours I am trying to apply.

                • Re: Conditional Formatting table with a range
                  Miguel Braga

                  I'm sorry, let me correct it

                   

                  if( num(

                  Pick(CompIndex

                  ,

                  ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

                    +

                        SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

                    )/

                        sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

                    )

                    ,

                     ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

                    +

                        SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

                    )/

                        sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

                    ))

                    ,'##0%') <0.70,

                   

                  RgbRed

                   

                  if( num(

                  Pick(CompIndex

                  ,

                  ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

                    +

                        SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

                    )/

                        sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

                    )

                    ,

                     ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

                    +

                        SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

                    )/

                        sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

                    ))

                    ,'##0%') >= 0.70 and

                  num(

                  Pick(CompIndex

                  ,

                  ((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

                    +

                        SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

                    )/

                        sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

                    )

                    ,

                     ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

                    +

                        SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

                    )/

                        sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

                    ))

                    ,'##0%') < 1,

                  RgbOrange,

                  RgbDarkGreen

                  )

                  )

                   

                  This way is done I didn't saw the Orange RGB.

                   

                  Hope this solves your issue.

                   

                  Regards,

                  MB