
1. Re: Merge multiple if condition in calculated field
Jim Dehner Jun 22, 2018 6:35 AM (in response to Mahendra Magare)Hi
then logic here would say that you are never going to get past the first block of clauses because the value in the 130 bucket will satisfy one of the 3 statements
IF [Bucket 130]<=0.02 THEN 'Green'
ELSEIF [Bucket 130] >0.02 and [Bucket 130] <=0.04 THEN 'Amber'
ELSEIF [Bucket 130] >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
Mahendra Magare Jun 22, 2018 7:13 AM (in response to Jim Dehner)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
garth.conrad Jun 22, 2018 7:13 AM (in response to Mahendra Magare)Beyond what Jim posted, the other thing to keep in mind is you are complicating you ifelse statements.
IF [Bucket 130]<=0.02 THEN 'Green'
ELSEIF [Bucket 130] >0.02 and [Bucket 130] <=0.04 THEN 'Amber'
ELSEIF [Bucket 130] >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
Mavis Liu Jun 22, 2018 7:18 AM (in response to Mahendra Magare)Hi Mahendra,
Do you have any buckets which are > 0.02?
Thanks,
Mavis

5. Re: Merge multiple if condition in calculated field
Mahendra Magare Jun 22, 2018 7:37 AM (in response to Mavis Liu) 
6. Re: Merge multiple if condition in calculated field
Mavis Liu Jun 22, 2018 7:45 AM (in response to Mahendra Magare)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 130])<=0.02 THEN 'Green'
ELSEIF avg([Bucket 130]) >0.02 and [Bucket 130] <=0.04 THEN 'Amber'
ELSEIF avg( [Bucket 130]) >0.04 THEN 'Red'
and so on.
Thanks,
Mavis

7. Re: Merge multiple if condition in calculated field
Mahendra Magare Jun 22, 2018 7:55 AM (in response to Mavis Liu) 
8. Re: Merge multiple if condition in calculated field
Mavis Liu Jun 22, 2018 7:57 AM (in response to Mahendra Magare)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 130])<=0.02 THEN 'Green'
ELSEIF avg([Bucket 130]) >0.02 and [Bucket 130] <=0.04 THEN 'Amber'
ELSEIF avg( [Bucket 130]) >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
Mahendra Magare Jun 22, 2018 8:04 AM (in response to Mavis Liu)Hi Mavis,
Below is the formula updated in calculated field and result. This is showing correct for Bucket 130 but not for all bucket. please see.
IF AVG([Bucket 130]) =0.00 THEN 'Black'
ELSEIF AVG([Bucket 130]) <=0.02 THEN 'Green'
ELSEIF AVG([Bucket 130]) <=0.04 THEN 'Amber'
ELSEIF AVG([Bucket 130]) >0.04 THEN 'Red'
ELSEIF AVG([Bucket 3160]) =0.00 THEN 'Black'
ELSEIF AVG([Bucket 3160]) <=0.02 THEN 'Green'
ELSEIF AVG([Bucket 3160]) <=0.04 THEN 'Amber'
ELSEIF AVG([Bucket 3160]) >0.04 THEN 'Red'
ELSEIF AVG([Bucket 6190]) =0.00 THEN 'Black'
ELSEIF AVG([Bucket 6190]) <=0.02 THEN 'Green'
ELSEIF AVG([Bucket 6190]) <=0.04 THEN 'Amber'
ELSEIF AVG([Bucket 6190]) >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
Mavis Liu Jun 22, 2018 8:13 AM (in response to Mahendra Magare)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 130]) =0.00 THEN 'Black'
ELSEIF AVG([Bucket 130]) <=0.02 THEN 'Green'
ELSEIF AVG([Bucket 130]) <=0.04 THEN 'Amber'
ELSEIF AVG([Bucket 130]) >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
meenu choudhary Jun 22, 2018 8:24 AM (in response to Mahendra Magare) 
12. Re: Merge multiple if condition in calculated field
Mahendra Magare Jun 26, 2018 6:16 AM (in response to Mavis Liu)Hi Mavis,
PFA the workbook.

Sample DPD.twbx 57.0 KB


13. Re: Merge multiple if condition in calculated field
Mavis Liu Jun 26, 2018 6:30 AM (in response to Mahendra Magare)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 130' and ([Pivot Field Values])=0.00 THEN 'Black'
ELSEIF [Pivot Field Names] = 'Bucket 130' and ([Pivot Field Values]) <=0.02 THEN 'Green'
ELSEIF [Pivot Field Names] = 'Bucket 130' and ([Pivot Field Values])<=0.04 THEN 'Amber'
ELSEIF [Pivot Field Names] = 'Bucket 130' and ([Pivot Field Values])>0.04 THEN 'Red'
ELSEIF [Pivot Field Names] = 'Bucket 3160' and ([Pivot Field Values])=0.00 THEN 'Black'
ELSEIF [Pivot Field Names] = 'Bucket 3160' and ([Pivot Field Values]) <=0.02 THEN 'Green'
ELSEIF [Pivot Field Names] = 'Bucket 3160' and ([Pivot Field Values])<=0.04 THEN 'Amber'
ELSEIF [Pivot Field Names] = 'Bucket 3160' and ([Pivot Field Values]) >0.04 THEN 'Red'
ELSEIF [Pivot Field Names] = 'Bucket 6190' and ([Pivot Field Values]) =0.00 THEN 'Black'
ELSEIF [Pivot Field Names] = 'Bucket 6190' and ([Pivot Field Values]) <=0.02 THEN 'Green'
ELSEIF [Pivot Field Names] = 'Bucket 6190' and ([Pivot Field Values]) <=0.04 THEN 'Amber'
ELSEIF [Pivot Field Names] = 'Bucket 6190' 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

Sample DPD_v10.2.twbx 80.4 KB


14. Re: Merge multiple if condition in calculated field
Mahendra Magare Jun 26, 2018 6:49 AM (in response to Mavis Liu)Thank you so much Mavis. This is perfect.