1 Reply Latest reply on May 4, 2016 3:01 AM by Yuriy Fal

    Looking up previous index value

    john.stjohn

      Hello:

       

      I have a simple problem from an excel or access database perspective but it seems to be complex in the Window or partition.

       

      I have a list of 100,000 team members.  Each has a number running one through 750 that corresponds to a compensation plan.  For each compensation plan there is an average fully loaded cost.

       

      The data looks like this

       

      Name                    Comp Plan            Fully loaded cost

      Jim Bob                 2                            100.00

      Sally Mae              7                            85.00

       

       

       

      Every comp plan number has the exact same Average cost so that if you are a 2, that is your average cost.  If I pull up comp plan 2 I get 100.00

       

      I need to pull up Jim Bob's comp plan which I can do, then lookup the previous number.  The plans are organized so that if Jim Bob is a 2 the next row down is the step beneath him in comp plans.  So the output I want to get is

       

      Name                  Comp Plan          FLC                Step down plan             Step down FLC

      Jim Bob               2                         100.00                 1                               80.00

       

       

      I can calculate the step down but I'm not able to run the index to grab the 80.00 for the previous plan.  The data is already set up to be in the correct order, if you are plan 600, we know that your salary is 400.00 and the plan number 599 is the next step down and the step down salary should be 385.00 for example.

       

      I simply can't index that step down to grab the next lower FLC.

       

      I appreciate any suggestions with some type of window in the index row.

       

      John

        • 1. Re: Looking up previous index value
          Yuriy Fal

          Hi John,

           

          It's all about data.

           

          Do you have (or can make) a table

          with all your Comp Plans and FLCs?

           

          If yes, add a column [Step Down Plan]

          to that table, and calculate values in it as:

           

          [Comp Plan] - 1

           

          Then you could join this table with itself

          on [t1].[Step Down Plan] = [t2].[Comp Plan]

          and join the resulting dataset to your main table --

          with just two columns in it [Name] and [Comp Plan] --

          on [Main Table].[Comp Plan] = [t1].[Comp Plan]

           

          Use the result as a datasource in Tableau.

          Everything else is simple.

           

          Yours,

          Yuri