5 Replies Latest reply on Jul 11, 2018 7:00 PM by minc650

    Calculation for Parameter

    minc650

      Hello,

       

      I'm stuck on the Threshold Calculation for the threshold Parameter.  My goal is that the  number of projects in 'New Region' and 'Expansion' > 3 then 'Red', the number of projects = 2 then 'Yellow', < 1 then 'Green'. My calculation for this parameter below. After I put it in the color, I only got two colors. I'm not sure what is wrong with it. Please help. Thanks. Workbook attached.

      Screen Shot 2018-07-11 at 8.59.41 AM.png

        • 1. Re: Calculation for Parameter
          Andy Piper

          CM S -- I'll assume you're on the DCD Resource Contractor sheet. The colors there are based on the [Project Color] calculated field. Currently it is calculated with:

           

          IF [# of Projects for Contractors] > 3 THEN "Red"

          ELSEIF [# of Projects for Contractors] = 3 THEN "Yellow"

          ELSE "Green"

          END

           

          The problem is there is no value greater than 3 in your [# of Projects for Contractors] field, so no project will turn Red. It will only show Yellow and Green.

          • 2. Re: Calculation for Parameter
            Andy Piper

            Looks like you uploaded a new workbook. Let me take a look at that one.

            • 3. Re: Calculation for Parameter
              minc650

              Hi Andy,

               


              Thank you for reply. It should be on the tab of Sheet 4. I created a parameter "Threshold Parameter", then a calculation for this parameter. Thank you.

              • 4. Re: Calculation for Parameter
                Andy Piper

                The way you have the calculated field looking at the parameter value and the regions and expansions, you will have just two results -- one that matches the criteria and one that does not (NULL).

                 

                If you want to include the parameter with the color options, then you need to include other options within each WHEN clause, like the following:

                WHEN 3 THEN   (IF ([# of New Region] =0  AND  [# of Expansion] >2) OR

                              ([# of New Region] >2  AND  [# of Expansion] =0) OR

                              ([# of New Region] >=1 AND  [# of Expansion] >1) OR

                              ([# of New Region] > 1 AND  [# of Expansion] >=1) THEN 1

                               ELSEIF ([# of New Region] = 1 AND [# of Expansion] = 1) OR

                              ([# of New Region] = 0 AND [# of Expansion] = 2) OR

                              ([# of New Region] = 2 AND [# of Expansion] = 0) THEN 0

                               ELSEIF ([# of New Region]  <2 AND  [# of Expansion] <1) OR

                               ([# of New Region]  <1 AND  [# of Expansion] <2) THEN -1 END)

                 

                Otherwise, you could create a calculated field without the parameter.

                IF ([# of New Region] =0  AND  [# of Expansion] >2) OR

                              ([# of New Region] >2  AND  [# of Expansion] =0) OR

                              ([# of New Region] >=1 AND  [# of Expansion] >1) OR

                              ([# of New Region] > 1 AND  [# of Expansion] >=1) THEN 'Red'

                ELSEIF ([# of New Region] = 1 AND [# of Expansion] = 1) OR

                              ([# of New Region] = 0 AND [# of Expansion] = 2) OR

                              ([# of New Region] = 2 AND [# of Expansion] = 0) THEN 'Yellow'

                ELSEIF ([# of New Region]  <2 AND  [# of Expansion] <1) OR

                               ([# of New Region]  <1 AND  [# of Expansion] <2) THEN 'Green'

                END

                • 5. Re: Calculation for Parameter
                  minc650

                  Hi Andy,

                   

                  This is correct! i've just tested.  Thank you so much.

                   

                  Best regards

                   

                  CM