1 2 Previous Next 19 Replies Latest reply on Dec 14, 2017 12:00 PM by Jim Dehner

    Nesting IF/CASE question

    Brandon Grell

      I'm not too sure on how to set this calculated field up as I have never had to do this before.  What I'm trying to accomplish is to set up a different Nested IF (elseif I think) for each individual code.

      For example:

      question1.PNG

      Basically what I need to do is be able to set up a different set up parameters (>=83, >=86 for the first code and then >=85, >=88 for the next etc.) for each of the metric codes.  I thought maybe I needed to use a CASE on the outside, but didn't know if you could use an IF statement on the inside of that.

      Also, the % Complete column is a calculated field created by the numerator/(denominator-exclusions).  The error its giving me is mixing agg and non agg.

       

      any ideas?

        • 1. Re: Nesting IF/CASE question
          Jim Dehner

          That's nasty

          without seeing your workbook it is a little difficult to see everything that is going on - could you attache a TWBX copy of your workbook (can be dummy data) -

          that said lets start with an assumption that you are creating this calculation on each measure because you have determined that you are stuck with a data structure will to allow you to pivot the data and that all the data is coming from a single source -

           

          the message in your calculation means  you will have to add an aggregation expression (Attr(), Min(), Max() on all the fields that are not aggregated (from above it looks like Metric Core is the one

          next the form of your inner  If statement will never get past the first clause any value greater than 83 will trigger the first Then clause to result in a 4 - you need to reverse the order of your clauses

           

          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: Nesting IF/CASE question
            Michael Daddona

            Hi, can you try the following in the beginning of your calc?

            I believe this will work for you.

            • 3. Re: Nesting IF/CASE question
              Brandon Grell

              Attached is the workbook, sorry forgot to throw that in.

              • 4. Re: Nesting IF/CASE question
                Jim Dehner

                Hi see the attached and below

                 

                 

                I turned your formula upside down and tableau will check against the decimal (not whole number) percentage

                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.

                1 of 1 people found this helpful
                • 5. Re: Nesting IF/CASE question
                  Brandon Grell

                  How do you then add in the next Metric_code?

                   

                  • 6. Re: Nesting IF/CASE question
                    Jim Dehner

                    Brandon - if all your groups are based on the same ranges you don't need to go to all this trouble

                    the single formula

                     

                    will produce this

                     

                    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.

                    1 of 1 people found this helpful
                    • 7. Re: Nesting IF/CASE question
                      Brandon Grell

                      They are not all based on the same ranges, each will have its own ranges.

                      • 8. Re: Nesting IF/CASE question
                        Brandon Grell

                        For example the CDCA1C range would be

                        >= 79.00%>= 86.00%>= 92.00%>= 96.00%
                        • 9. Re: Nesting IF/CASE question
                          Joe Oppelt

                          The first ELSE 0 is closing off your IF statement, so Tableau doesn't know what to do with the ELSEIF.

                           

                          Take it out.  Just have one at the end when you are done with all your ELSEIF chunks.

                          1 of 1 people found this helpful
                          • 10. Re: Nesting IF/CASE question
                            Jim Dehner

                            If there is no relationship then you will have one very long nested if statement with each Metric Code Group separated by an elseif statement

                             

                            that would look something like this

                             

                            IF  Attr([Metric Code]) = 'CBP' THEN

                             

                             

                             

                             

                                                        if [% Complete] >= .94 THEN '6'

                                                        ELSEIF [% Complete] >= .90 THEN '5'

                                                        ELSEIF [% Complete] >= .86 THEN '4.5'

                                                        ELSEIF [% Complete] >= .83 THEN '4'

                                                        ELSE '3'  end

                             

                             

                            elseif Attr([Metric Code]) = 'ART' THEN

                             

                             

                                                         if [% Complete] >= .94 THEN '6'

                                                        ELSEIF [% Complete] >= .90 THEN '5'

                                                        ELSEIF [% Complete] >= .86 THEN '4.5'

                                                        ELSEIF [% Complete] >= .83 THEN '4'

                                                        ELSE '3'  end

                             

                             

                            with the correct values in it and it would close out at the very end with

                             

                                                     else '0' end

                             

                            Do the values change or are they very static? 

                             

                            Jim

                            • 11. Re: Nesting IF/CASE question
                              Joe Oppelt

                              I unmarked my reply as correct and set Jim's to the Correct answer.  I just answered a final syntax question.  Jim answered the major part of the question.

                              • 12. Re: Nesting IF/CASE question
                                Brandon Grell

                                They are static.  Thank you Jim for your help and also Joe for the assist.

                                • 13. Re: Nesting IF/CASE question
                                  Jim Dehner

                                  Thanks Joe - if you are still following - each of his metric codes has different values which would lead to a very  long nested if - can you think of a better way?

                                  Jim

                                  • 14. Re: Nesting IF/CASE question
                                    Joe Oppelt

                                    If there is some data-driven way to know the range limits, it could probably be done programmatically in the calc.  If they are stored by [Metric Code] then there could be LODs to store those values at the [Metric Code] level, and then they could be plugged into inner "IF" code without the need for the individual checks for the [Metric Code] value at all.

                                    1 2 Previous Next