1 2 Previous Next 15 Replies Latest reply on Jun 26, 2018 7:15 AM by Mavis Liu

# Merge multiple if condition in calculated field

I have 4 column header naming Bucket 1-30, Bucket 31-60, Bucket 61-90, NPA. i want to apply below mention color code all of them. facing syntax error. please suggest.

IF [Bucket 1-30]<=0.02 THEN 'Green'

ELSEIF [Bucket 1-30] >0.02 and [Bucket 1-30] <=0.04 THEN 'Amber'

ELSEIF [Bucket 1-30] >0.04 THEN 'Red'

ELSEIF [Bucket 31-60] <=0.02 THEN 'Green'

ELSEIF [Bucket 31-60] >0.02 and [Bucket 31-60] <=0.04 THEN 'Amber'

ELSEIF [Bucket 31-60] >0.04 THEN 'Red'

ELSEIF [Bucket 61-90] <=0.02 THEN 'Green'

ELSEIF [Bucket 61-90] >0.02 and [Bucket 61-90] <=0.04 THEN 'Amber'

ELSEIF [Bucket 61-90] >0.04 THEN 'Red'

ELSEIF [NPA] <=0.02 THEN 'Green'

ELSEIF [NPA] >0.02 and [Bucket 61-90] <=0.04 THEN 'Amber'

ELSEIF [NPA] >0.04 THEN 'Red'

ELSE 'Black' END

• ###### 1. Re: Merge multiple if condition in calculated field

Hi

then logic here would say that you are never going to get past the first block of clauses because the value in the 1-30 bucket will satisfy one of the 3 statements

IF [Bucket 1-30]<=0.02 THEN 'Green'

ELSEIF [Bucket 1-30] >0.02 and [Bucket 1-30] <=0.04 THEN 'Amber'

ELSEIF [Bucket 1-30] >0.04 THEN 'Red'

I'm not certain I understand the goal - are you trying to color code all the "buckets" based on the Max or the Min or the average of the 4?

if so make that calculation and use it as the trigger in the color code calculation

Jim

If this posts 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: Merge multiple if condition in calculated field

I am trying to color code the all buckets in that criteria. but not able to beyond first bracket. please suggest if it is possible to update that color code based on that selection.

• ###### 3. Re: Merge multiple if condition in calculated field

Beyond what Jim posted, the other thing to keep in mind is you are complicating you ifelse statements.

IF [Bucket 1-30]<=0.02 THEN 'Green'

ELSEIF [Bucket 1-30] >0.02 and [Bucket 1-30] <=0.04 THEN 'Amber'

ELSEIF [Bucket 1-30] >0.04 THEN 'Red'

If you fail the first part of the if statement then you know the value is above 0.02, as such your second statement only needs to check if the value is <= 0.04.

The last thing to keep in mind is that you are checking the full range of values for each bucket. The last statement of else ‘Black’ would only be used if you have a null, is that the intent?

G

• ###### 4. Re: Merge multiple if condition in calculated field

Hi Mahendra,

Do you have any buckets which are > 0.02?

Thanks,

Mavis

• ###### 5. Re: Merge multiple if condition in calculated field

Hi Mavis,

I have below buckets.

1.     Bucket 1-30

2.     Bucket 31-60

3.     Bucket 61-90

4.     NPA

What i want basically is, to color code these 4 buckets based on below formula.

IF [Bucket 1-30]<=0.02 THEN 'Green'

ELSEIF [Bucket 1-30] >0.02 and [Bucket 1-30] <=0.04 THEN 'Amber'

ELSEIF [Bucket 1-30] >0.04 THEN 'Red' • ###### 6. Re: Merge multiple if condition in calculated field

Hi Mahendra,

Your calculation is looking at the data on a row by row basis.

So it looks at each record and then applies the colour. Whereas in your view, you are using  Avg. Bucket... which has been aggregated.

So you'll need to also aggregate your calculation to include average, e.g.:

IF avg( [Bucket 1-30])<=0.02 THEN 'Green'

ELSEIF avg([Bucket 1-30]) >0.02 and [Bucket 1-30] <=0.04 THEN 'Amber'

ELSEIF avg( [Bucket 1-30]) >0.04 THEN 'Red'

and so on.

Thanks,

Mavis

• ###### 8. Re: Merge multiple if condition in calculated field

Hi Mahendra,

This is because you need to do this for every single row.

You've only added in an avg for the first row. Please add in avg for every single time the orange field appears.

Please see below in bold:

iF avg( [Bucket 1-30])<=0.02 THEN 'Green'

ELSEIF avg([Bucket 1-30]) >0.02 and [Bucket 1-30] <=0.04 THEN 'Amber'

ELSEIF avg( [Bucket 1-30]) >0.04 THEN 'Red'

If you do this for every row, then the error should disappear. It only has a warning now because you have one row which is aggregated and nothing else is.

Thanks,

Mavis

• ###### 9. Re: Merge multiple if condition in calculated field

Hi Mavis,

Below is the formula updated in calculated field and result. This is showing correct for Bucket 1-30 but not for all bucket. please see.

IF AVG([Bucket 1-30]) =0.00 THEN 'Black'

ELSEIF AVG([Bucket 1-30]) <=0.02 THEN 'Green'

ELSEIF AVG([Bucket 1-30]) <=0.04 THEN 'Amber'

ELSEIF AVG([Bucket 1-30]) >0.04 THEN 'Red'

ELSEIF AVG([Bucket 31-60]) =0.00 THEN 'Black'

ELSEIF AVG([Bucket 31-60]) <=0.02 THEN 'Green'

ELSEIF AVG([Bucket 31-60]) <=0.04 THEN 'Amber'

ELSEIF AVG([Bucket 31-60]) >0.04 THEN 'Red'

ELSEIF AVG([Bucket 61-90]) =0.00 THEN 'Black'

ELSEIF AVG([Bucket 61-90]) <=0.02 THEN 'Green'

ELSEIF AVG([Bucket 61-90]) <=0.04 THEN 'Amber'

ELSEIF AVG([Bucket 61-90]) >0.04 THEN 'Red'

ELSEIF AVG([NPA]) =0.00 THEN 'Black'

ELSEIF AVG([NPA]) <=0.02 THEN 'Green'

ELSEIF AVG([NPA]) <=0.04 THEN 'Amber'

ELSEIF AVG([NPA]) >0.04 THEN 'Red'

END • ###### 10. Re: Merge multiple if condition in calculated field

Hi Mahendra,

This is because it's applying the same colour each time based on your criteria. Because your criteria only uses this part:

IF AVG([Bucket 1-30]) =0.00 THEN 'Black'

ELSEIF AVG([Bucket 1-30]) <=0.02 THEN 'Green'

ELSEIF AVG([Bucket 1-30]) <=0.04 THEN 'Amber'

ELSEIF AVG([Bucket 1-30]) >0.04 THEN 'Red'

It fits the criteria so there's no reason for the if statement to go past this. The calculation will need to be updated but we'll also need to review the way your data is shaped.

Please attach the workbook for us to review.

Thanks,

Mavis

• ###### 11. Re: Merge multiple if condition in calculated field

Hi Mahendra,

You can try below approach:

Since the condition for color is same for all the three buckets,

1. drag one of the measure to color

2. Edit Color-->Custom Diverging and do the below settings: • ###### 12. Re: Merge multiple if condition in calculated field

Hi Mavis,

PFA the workbook.

• ###### 13. Re: Merge multiple if condition in calculated field

Hi Mahendra,

I have pivoted your data: Where there will be a new field called pivot field names and pivot field values. You can rename these, for now I've left them as is.

Then updated the calculation:

IF [Pivot Field Names] = 'Bucket 1-30' and ([Pivot Field Values])=0.00 THEN 'Black'

ELSEIF [Pivot Field Names] = 'Bucket 1-30' and ([Pivot Field Values]) <=0.02 THEN 'Green'

ELSEIF [Pivot Field Names] = 'Bucket 1-30' and ([Pivot Field Values])<=0.04 THEN 'Amber'

ELSEIF [Pivot Field Names] = 'Bucket 1-30' and ([Pivot Field Values])>0.04 THEN 'Red'

ELSEIF [Pivot Field Names] = 'Bucket 31-60' and ([Pivot Field Values])=0.00 THEN 'Black'

ELSEIF [Pivot Field Names] = 'Bucket 31-60' and ([Pivot Field Values]) <=0.02 THEN 'Green'

ELSEIF [Pivot Field Names] = 'Bucket 31-60' and ([Pivot Field Values])<=0.04 THEN 'Amber'

ELSEIF [Pivot Field Names] = 'Bucket 31-60' and ([Pivot Field Values]) >0.04 THEN 'Red'

ELSEIF [Pivot Field Names] = 'Bucket 61-90' and ([Pivot Field Values]) =0.00 THEN 'Black'

ELSEIF [Pivot Field Names] = 'Bucket 61-90' and ([Pivot Field Values]) <=0.02 THEN 'Green'

ELSEIF [Pivot Field Names] = 'Bucket 61-90' and ([Pivot Field Values]) <=0.04 THEN 'Amber'

ELSEIF [Pivot Field Names] = 'Bucket 61-90' and ([Pivot Field Values])>0.04 THEN 'Red'

ELSEIF [Pivot Field Names] = 'NPA' and ([Pivot Field Values]) =0.00 THEN 'Black'

ELSEIF [Pivot Field Names] = 'NPA' and ([Pivot Field Values]) <=0.02 THEN 'Green'

ELSEIF  [Pivot Field Names] = 'NPA' and ([Pivot Field Values]) <=0.04 THEN 'Amber'

ELSEIF  [Pivot Field Names] = 'NPA' and ([Pivot Field Values]) >0.04 THEN 'Red'

END Thanks and please see attached.

Mavis

1 of 1 people found this helpful
• ###### 14. Re: Merge multiple if condition in calculated field

Thank you so much Mavis. This is perfect.

1 2 Previous Next