2 Replies Latest reply on Aug 16, 2013 8:05 AM by Dana Withers

    How to work with multiple records

    Steve DeLillo

      Hi I am hoping someone can help me out. I have a table with all employees and their status for each month, each month is a new record so the data will look something like this:

       

      EmpName   Status   Month   Dept

      Mike             Term     June     NJ

      Steve           Active    June     NY

      Joe              Active    June     NJ

      Mike            Term      July     NJ

      Steve           Term      July     NY

      Joe              Active    July      NJ

      Jim              Active    July     NY

       

      I am looking to create a report that will be able to show me how many employees were added and how many were changed to term in a specific month. Each month I append the table with the newest month's data.

       

      I am trying to do something to identify that Steve was Active in June and Term in July, meaning he was a termination during July. I am also looking to identify that Jim was not on the file in June but was added in July.

       

      Any help would be appreciated.

       

      Thanks,
      Steve

        • 1. Re: How to work with multiple records
          Pedro Machado

          Steve,

           

          If possible, I would do this on the database. Perhaps creating a new view.

           

          If not, you could try a self join via custom SQL. Note this is not in tableau Custom SQL syntax and hasn't been tested, but it should give you an idea. This assumes there is an EmployeeID that is unique each month (no duplicate IDs for in any given month).

           

          First, create a result set that has the employee status for last month and for the current month on the same record.

           

          SELECT    coalesce(c.EmpID,p.EmpID) as EmpID,

                    coalesce(c.EmpName,p.EmpName) as EmpName,

                    p.Dept as prev_dept,

                    p.Status as prev_status,

                    c.Dept as curr_dept,

                    c.Status as curr_status

          FROM employee_table p FULL OUTER JOIN employee_table c

          ON p.Month = <last_month>

          AND c.Month = <this_month>

          AND p.EmpID = c.EmpID ;

           

          Then you can apply logic in Tableau via calculated fiends to decode the data. For example:

           

          IF isnull(prev_status) AND curr_status = "Active" THEN "NEW HIRE"

          ELSEIF prev_status = "Active" and curr_status = "Term" THEN 'TERMINATED"

          ELSEIF prev_status = "Active" and curr_status = "Active" THEN 'CONTINUING"

          ...

          END

           

          You will have to see all the possible cases and add logic to handle them.

           

          Does this make sense?

           

          Thanks,


          Pedro

          • 2. Re: How to work with multiple records
            Dana Withers

            Hi Steve,

             

            Pedro gave a good solution using custom SQL, but I wanted to add that it is also just possible in your current data set using a table calculation. I've given the statuses a number so you can see the difference compared to previous month (using the "difference" table calc). I added a bit to your data set to give me more to play with, but I assume that you'll just build in a filter/selection of some sort to pick the months.

             

            See how that works for you.

             

            Enjoy!

             

            Dana