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

    Mahendra Magare

      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
          Jim Dehner

          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
            Mahendra Magare

            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

              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
                Mavis Liu

                Hi Mahendra,

                 

                Do you have any buckets which are > 0.02?

                 

                Thanks,

                 

                Mavis

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

                  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
                    Mavis Liu

                    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

                    • 7. Re: Merge multiple if condition in calculated field
                      Mahendra Magare

                      Tried doing this shows below error.

                       

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

                        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
                          Mahendra Magare

                          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
                            Mavis Liu

                            Hi Mahendra,

                             

                            This is because it's applying the same colour each time based on your criteria.

                             

                            2018-06-22_16h10_06.png

                             

                            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
                              meenu choudhary

                              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
                                Mahendra Magare

                                Hi Mavis,

                                 

                                    PFA the workbook.

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

                                  Hi Mahendra,

                                   

                                  I have pivoted your data:

                                  2018-06-26_14h28_48.png

                                   

                                  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

                                   

                                   

                                  2018-06-26_14h27_59.png

                                   

                                  Thanks and please see attached.

                                  Mavis

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

                                    Thank you so much Mavis. This is perfect.

                                    1 2 Previous Next