4 Replies Latest reply on Feb 17, 2016 6:54 AM by Tony Ball

    Calculated Field for Pass/Fail Criteria

    Tony Ball

      Hi All,

       

      I wonder if you could help me? I'm struggling a little bit with a dashboard requirement. I'll try to explain as best I can.

       

      I have a list of service names which have the following attributes associated to them

      Dimension [Check Type]  - Business Owner, Review Date, Service Owner and Short Description

       

      I've written a custom SQL query that wraps some rules around whether the data in the field has passed or failed an integrity test. This is listed in Tableau as

      Measure [Check Status] -  1 = Pass (alias) or 0 = Fail (alias)

       

      What I would like to do next is start to count the number of service names that have passed all 4 criteria (business owner, review date, service owner and short description)

      I would then like to count 3 passes and 1 fail, 2 passes and 2 fails, 1 pass and 3 fails and all fails on the criteria.

       

       

      I've come up with this kind of calculated field so far but it doesn't quite achieve what I need.

       

      IF [CheckType] = 'Business Owner' AND STR([CheckStatus]) = '1' THEN 'Pass'

      ELSEIF [CheckType] = 'Review Date' AND STR([CheckStatus]) = '1' THEN 'Pass'

      ELSEIF [CheckType] = 'Service Owner' AND STR([CheckStatus]) = '1' THEN 'Pass'

      ELSEIF [CheckType] = 'Short Description' AND STR([CheckStatus]) = '1' THEN 'Pass'

      ELSEIF STR([CheckStatus]) = '0' THEN 'Fail'

      END

       

      Please can someone help to point me in the right direction? I would really appreciate it.

        • 1. Re: Calculated Field for Pass/Fail Criteria
          Tableau kumar

          Hi,

           

          Would you please share little sample data which is directly coming from DB without writing Query.

           

          Mean while try out whether the following syntax will help you.

          we can create Calculated fields instead of writing SQL Queries.

          {fixed: sum(if [Check Status] =1 then 1 else 0 end)}

           

          Best Regards

          Kumar

          • 2. Re: Calculated Field for Pass/Fail Criteria
            Tony Ball

            Hi Kumar,

             

            Thanks for your reply. I played around with the query a little more last night, but now I'm a different kind of stuck!

             

            I came up with the following calculated field.

            IF

            COUNT([CheckType]) = 4

            Then

            "All Passed"

            ELSEIF  COUNT([CheckType]) <4

            Then

            "Failed"

            END

             

            This kind of works but I am unable to use the calculated field created as a filter? It will only let me place it in rows or columns. I get the following result so I know it works - however I really need to be able to have filter functionality for the calculations - can anyone come up with a better way to do this? Here is a screenshot of the worksheet proving that this does work.

             

            • 3. Re: Calculated Field for Pass/Fail Criteria
              Tableau kumar

              Hi,

              Drag the cnt([Check Type]) on to filter shelf then select "sum" then select Range" tab then define 1, 3 are the limits.

               

              or

              Once you create the calculated field using the above syntax, Right click on that calculated field then click on "Show Quick Filter" then define the range from 1 to 3. It will show only Failed perons data on the view.

               

              Customer segmentation in Tableau

               

              Best Regards

              Kumar

              1 of 1 people found this helpful
              • 4. Re: Calculated Field for Pass/Fail Criteria
                Tony Ball

                Hi Kumar,

                 

                Thanks for all your help on this one. In the end I wrote a custom SQL query to address the issue I was facing. However your ideas on how to solve the problem within Tableau helped me to come up with a solution. So will mark your last comment as correct.