4 Replies Latest reply on Jun 23, 2016 6:37 AM by brett devine

    Calculating Employees Grade prior to Change

    brett devine

      Apologies in advance if this has already been addressed already, but I haven't come across a way to solve for this yet (I'm new to Tableau).



      I have the following table of history within a system that tracks changes to a given employee's HR record..



      Name      Eff Date     Action     Action Date     Grade

      John D     1/1/2016     DTA     1/1/2016                9

      John D     2/1/2016     PRO     2/1/2016              10

      John D     2/15/2016     DTA    2/15/2016            10

      Steve A     1/1/2016     DTA     1/1/2016               7

      Steve A     2/5/2016     PRO     2/5/2016               9


      I'm looking to identify the grade PRIOR to the employee receiving a promotion (where 'Action' = "PRO").

      For example, I'm looking to return grade 9 for John D, as that was the grade he was prior to his promotion row. Steve's row would return grade 7, and so on.



      Hopefully this makes sense,



      I appreciate the help!

        • 1. Re: Calculating Employees Grade prior to Change



          If you are presenting the data in table format,

          maybe you can try this calculation:

          IF ATTR([Action])="PRO"

          THEN LOOKUP(ATTR([Grade]),-1)



          It will lookup the Grade in the Row before the PRO.

          • 2. Re: Calculating Employees Grade prior to Change
            brett devine



            I appreciate your follow-up. I should've done a better job clarifying my problem.


            The underlying raw data is structured in the following way:





            I created a table in Tableau that looks like this:


            Name     New Grade     Old Grade

            John D          8                    ?

            Steve A         10                  ?

            Peter T          7                    ?




            The "Old Grade" would have to be determined logically. I don't believe the Table Calculation is the solution I'm seeking.  I'm needing help or needing to know if its even possible to basically read the raw underlying data and logically return the grade prior to the promotion row within the raw data set.


            I'd prefer to NOT calculate this in excel and I'm wondering if there's a way for Tableau to do this? Would this require the use of RAW SQL?


            I hope this makes sense,





            • 3. Re: Calculating Employees Grade prior to Change



              My apologies, I didn't quite catch.

              It is possible that table calculations won't work for your setup.

              Just wanted to clarify the results you are seeking.

              Using the table calc method, I got the below results (sheet2 of the attached)

              but they do not match your results table above:

              John D NewGrade of 8 vs. 10

              Steve A NewGrade of 10 vs 9.

              Please clarify how those New Grades are derived from the raw data.

              Sorry for another round.



              1 of 1 people found this helpful
              • 4. Re: Calculating Employees Grade prior to Change
                brett devine



                This clarifies perfectly. This really helped me bridge a knowledge gap as well. I really appreciate your willingness to assist with this.