8 Replies Latest reply on Jul 10, 2019 4:41 PM by John Sarantos

    IF statement Calculated Field

    nana.taylor

      H

       

      I am trying to use an if statement to create a calculated field in tableau that check to see if multiple fields are filled up/have values then "complete" if there is no value in either of the fields then "incomplete".

       

      I also want to create another field that calculate the percent of completion. So for instance if 1 out of 2 fields is null then its 50% complete.

      Any help will be much appreciated.

       

      Thank you

        • 1. Re: IF statement Calculated Field
          Ken Flerlage

          One calculated field should do this. Something like:

           

          (IF ISNULL([Field 1]) = True THEN 0 ELSE 1 END +

          IF ISNULL([Field 2]) = True THEN 0 ELSE 1 END +

          IF ISNULL([Field 3]) = True THEN 0 ELSE 1 END) /

          [Total Number of Fields]

           

          If the value is 1 then all have a value. If not then the value is your percentage.

          1 of 1 people found this helpful
          • 2. Re: IF statement Calculated Field
            Joe Oppelt

            If I understand correctly, I think this is what you need:

             

            ( IF NOT ISNULL([measure 1]) then 1 else 0 END)
            +

            ( IF NOT ISNULL([measure 2]) then 1 else 0 END)

            +

            ( IF NOT ISNULL([measure 3]) then 1 else 0 END)

            ...

             

            Add up 1s and 0s for as many measures as you need to check.

             

            If the sum of that calc is equal to the number of measures, they none of the measures are NULL.

             

            Based on that, you can then set a COMPLETE or INCOMPLETE value.

             

            I would do it in two calcs, but you could combine all that into one calc if you wanted.

            1 of 1 people found this helpful
            • 3. Re: IF statement Calculated Field
              nana.taylor

              Thanks for the reply. I like your calculation however it should be "1" or "complete" if there are values in all the fields/measures I am using. so for instance if all the 5 fields I am using have values in them or is not null then complete. if one of the fields is missing a value even if the other fields have values then "incomplete". I have 12 fields that the if statement should reference to see if all fields is complete/have values.

              • 4. Re: IF statement Calculated Field
                nana.taylor

                Thanks for the reply. I like your calculation however it should be "complete" if there are values in all the fields/measures I am using. so for instance if all the 5 fields I am using have values in them or is not null then mark as complete. if one of the fields is missing a value or is null even if the other fields have values or is not null then mark as "incomplete". I have 12 fields that the if statement should reference to see if all fields is complete/have values.

                • 5. Re: IF statement Calculated Field
                  Naveen B

                  Hi Taylor,

                   

                  Could you please attach sample workbook in twbx format to help you more

                   

                  BR,

                  NB

                  • 6. Re: IF statement Calculated Field
                    Joe Oppelt

                    What I proposed is just like Ken's.  You have to look at each measure individually, and if not null then add a 1.  If you have 12 of these, then you'll have 12 chunks of IF-THEN-1-ELSE-0.  And if all of that adds up 12, then all measures are complete.  And if it adds up to 12, then you take it one more step to  say,

                     

                    IF [check them all calc] = 12 then "Complete" else "Incomplete" END

                    • 7. Re: IF statement Calculated Field
                      John Sarantos

                      OK - try this on for size:

                       

                      1st calc for Complete/Incomplete:

                      IIF(isnull([Field A])

                      or isnull([Field B])

                      or isnull([Field C])

                      ... (Do this for all of your fields

                      or isnull([Field Z]), 'Incomplete', 'Complete')

                       

                      2nd calc for % Complete:

                      SUM(

                      IIF(isnull([Field A])

                      or isnull([Field B])

                      or isnull([Field C])

                      ... (Do this for all of your fields)

                      or isnull([Field Z]), 0, 1))

                      /

                      COUNT(

                      IIF(isnull([Field A])

                      or isnull([Field B])

                      or isnull([Field C])

                      ... (Do this for all of your fields)

                      or isnull([Field Z]), 0, 1))

                       

                      Does that get at what you're going for?

                      1 of 1 people found this helpful
                      • 8. Re: IF statement Calculated Field
                        Chris McClellan

                        Some dummy data would be great, but my (untested) approach is different ....

                         

                        If isnull([Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6]+[Field7]+[Field8]+[Field9]+[Field10]+[Field11]+[Field12])

                        then "incomplete"

                        else "complete"

                        end

                        1 of 1 people found this helpful