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

# Calculation for Parameter

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.

• ###### 1. Re: Calculation for Parameter

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

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

• ###### 3. Re: Calculation for Parameter

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

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

Hi Andy,

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

Best regards

CM