3 Replies Latest reply on Apr 19, 2018 4:11 PM by Cynthia Derksen

    IF THEN AND BETWEEN Calculation

    Cynthia Derksen

      I have a calculation that I am using to color my table.  It has four colors Red for > 5% below goal (Red); Yellow is within 5% of goal; and

      Light and Dark Green for those who have met goal.    Only the green colors of my formulas work. I reversed the order of the formula hoping that

      make it work.  I am sure there is a simple answer that I can't figure out.  Please share your knowledge.


      //Greater than 5% below goal

      If ([Rate]<([Goal]*0.95)) THEN "Red"


      //Less than Goal but within 5% of Goal

      ELSEIF (([Rate]<[Goal]) AND ([Rate]>([Goal]*0.95))) THEN "Yellow"


      // less than 100% and equal to or greater than Goal

      ELSEIF ([Rate]>=[Goal])THEN "Light Green"


      // outstanding score is 100%

      ELSEIF ([Rate]=1) THEN "Dark Green"




      I am trying to do conditional formatting on my table.

        • 1. Re: IF THEN AND BETWEEN Calculation
          Wesley Magee


          Are [Rate] and [Goal] aggregates? Without seeing the actual data my first thought would be to wrap them in an appropriate aggregate (probably SUM or AVG) depending on your data.


          Did that help?



          If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: IF THEN AND BETWEEN Calculation
            Chris McClellan

            Is [Rate] and [Goal] the same datatype and the same order of magnitude ?


            Usually in this sort of situation I create a new sheet showing [Rate], [Goal] and [the calculated field] to see where the problem is in the logic.


            Could it be a aggregated/non-aggregated issue ?


            Some sample data would be great if you need more help

            • 3. Re: IF THEN AND BETWEEN Calculation
              Cynthia Derksen

              This is the current months data so, it selected by Max Date.

              The rate is simple num/denom =Rate for numerous measure.

              There are 6 locations and one of them is the GOAL.

              The goal is a national benchmark which changes between each measure.


              So, I created calculation of Goal is culled by

              IF ATTR([Location]="GOAL") THEN ([Rate]) END


              My table design is all locations are columns and the measures are rows with the T label being AGG(Rate)


              I have add an attachment.