4 Replies Latest reply on May 7, 2018 4:42 PM by Anisha Jhamb

    Calculating: conditional formatting color background based- if then or case when - neither work

    Cynthia Derksen

      Hello,

       

      Example exported in 10.5 to 10.2.   I would like my colors to be red, yellow green and dark green based on my goal.

      My goal changes for each performance measure that is why I thought I formula would work best.

       

      The step formatting doesn't work for this scenario because the steps would need to be a calculation.  Right?

       

      I can't figure it out so, I am pretty stuck.  Any assistance or direction would be of appreciated.

        • 1. Re: Calculating: conditional formatting color background based- if then or case when - neither work
          Anisha Jhamb

          Hi,

           

          Attached is the solution workbook and below is the screenshot.

          I created 3 calculations: Rate 1, Goal Rate 1 and Color and used them in the worksheet. Let me know if you have any questions!

           

          Please note that the Blue corresponding to Null is due to the fact that you are not handling some cases in your of conditions.

          For Ex- CA State Rate for Measure 1 is 99.5% but as per your IF condition Less Than 5% of 99.8% (Goal Rate) is 94.8%, so, (Rate < Goal Rate*.95) amounts to False. I think you need to re-check the logic for Red and Yellow as both are pointing to less than 5%.

           

          Red ->  (Rate < Goal Rate*0.95)  and  Yellow -> (Rate < Goal Rate*0.9499999)

          1 of 1 people found this helpful
          • 2. Re: Calculating: conditional formatting color background based- if then or case when - neither work
            Cynthia Derksen

            Thank you so much!  I have been playing with that workbook and formulas that i must have deleted the second half of the yellow.  Yellow is was supposed to be  [Rate]>([Goal Rate]*0.9499999) AND [Rate]< [Goal Rate] then  'Yellow'.  If you hadn't heard my shout of glee.  Let's just say I disturbed the neighbor of my cube.   I have not used the FIXED and Max like you have.  Would you share the source?   I am always seeking to learn.  Again, Thank you so very much. 

            • 3. Re: Calculating: conditional formatting color background based- if then or case when - neither work
              Anisha Jhamb

              Glad that it worked! I noticed one more thing in your calculation.

               

              In your IF ELSE structure,

               

              Rate >= Goal Rate condition is appearing above Rate = 1 condition

               

              The cursor would never go to the last one.

               

              For Ex - If Rate is 1 (ie 100%), Rate>= Goal Rate will always get satisfied and the cursor would never get to Rate = 1

               

              • 4. Re: Calculating: conditional formatting color background based- if then or case when - neither work
                Anisha Jhamb

                Let me explain how I approached the problem.

                 

                The first thing i noticed in the worksheet was that the Goal Rate was only coming up for City = 'Goal', for others its Null.

                Hence, the cursor never satisfies to any IF Condition containing Goal Rate for cities other than 'Goal' because it compares against 'Null'.

                Screenshot below of your worksheet, i just added Goal rate to Text to see what value its generating

                 

                 

                 

                What we really want is to have the Goal Rate for every data point but the calculation should only consider City='Goal'. This is the reason I used Fixed LOD calc. see similar screenshot below of my worksheet

                 

                I created LOD calc for Rate to bring it on the same level of aggregation so that it would allow to compare both fields in your IF ELSE condition.

                 

                 

                And, then tweaked the IF conditions to change the order of Rate = 1 condition, so that its not overridden by Rate >= Goal Rate condition.

                 

                Let me know if you have any further questions.