5 Replies Latest reply on Jan 25, 2016 9:50 AM by Eric Decker

    Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

    Eric Decker

      Calling all Tableau Experts!


      I have 10 months of data, January thru October. I’m trying to write a table calculation that sets a new goal for First Pass Yield that’s 50% higher than the previous high First Pass Yield monthly aggregate. That’s a bit of a mouthful, so here’s an example to better illustrate what I’m looking for (Reference the below table for the values I’m talking about):


      FPY Reference Table.jpg



      First, obviously, January has no previous lookup value to measure against so I’m setting that Goal value to just be .5 (just so there are no Null values in the table calc).


      For February’s goal, the calculation should be January’s FPY actual plus 50%, which in this case would be 77.27%.


           Goal % Calc = ((1-((Lookup(Previous High Value)))*.5)+(Lookup([Previous High Value)))


      Now, because February’s FPY was lower than the first month, the goal would still be set at 77.27%. This trend continues until we get to June where we see our first FPY value that surpasses our first FPY max. Therefore, the goal for July should be 50% higher than the newest FPY high of 72.97%, giving us a new goal of 86.49%. Lastly we see yet one more increase to the monthly FPY in August, which bumps up the new goal to 87.06%.


      Now, it would be pretty simple to just find the window_max of FPY, add 50% and make that goal for all months, but I want to maintain the integrity of the goal values for the time frames they were applicable. (This way I can keep and track the history of our goal progressions)


      Does anyone have any suggestions how to write a table calc (or multiple table calcs) to accomplish this task? I’ve been trying to use Lookups, Window_Maxs, etc. in multilple calculations but I’m just not able to get all the way there.


      Attached is a Packaged workbook using the above table to show what the desired results for the goal bars *should* be.


      Please help!!