2 Replies Latest reply on Sep 13, 2016 10:15 AM by jeff stoltz

    calculated field to solve?

    jeff stoltz

      Hi Everyone - I am having a slight issue trying to do a calculation where I sum up values based on conditions.  In the attached twbx file there are three columns: 1. employee ID (where the same ID can live on multiple rows), 2. Nominee Employee ID (can also live on multiple rows) and 3. Nomination Readiness (either as Ready in 1-3 years, Ready Now or Ready in 3 - 5 years). 

       

      What we are trying to calculate is the number of employees that have at least 2 Ready now or Ready in 1 - 3 years nominees (ie 2 Ready now or 2 Ready in 1-3 years or 1 Ready now and 1 Ready in 1-3 years).

       

      In excel, a pivot table and some basic formulas calculates that there are 3 employees (1, 2, and 3) that have at least 2 Ready now or Ready in 1-3 years.

       

      Please let me know if this type of calculation is possible in tableau.

      thanks

      jeff

        • 1. Re: calculated field to solve?
          Michel Caissie

          Jeff,

           

          You need first to flag the Nomination Readiness you want to consider using  the case function

          Next you want to Sum the result for each Employee ID  using LOD.

          Next you want to flag each employee where the previous SUM is >= 2

          And finally  SUM the number of employee.

           

          You can get this with the following calculation;

          SUM(

              if { FIXED [Employee ID]: SUM(

                                                                   case [Nomination Readiness]

                                                                   when 'Ready Now' then 1

                                                                   when 'Ready in 1 - 3 years' then 1

                                                                   end

                                                                  )

                  }

              >= 2

              then 1

              else 0

              end

          )

           

          See in the attached

           

          Michel

          • 2. Re: calculated field to solve?
            jeff stoltz

            Michel - thank you very much for your explanation and calculation.  It did exactly what I needed it to.

             

            thanks again

            jeff