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

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):

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.

• ###### 1. Re: Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

You lost me at 54.55% + 50% = 77.27% (for Feb)  and 72.97% + 50% = 86.49% (for July)

Maybe I'm looking at it wrong, can you send the data in Excel (assuming you have the calculation working there)

• ###### 2. Re: Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

Chris,

Thanks for the reply and willingness to look into this. I probably didn't articulate the 50% portion of the calculation very well the first time. Because First Pass Yield has a maximum percentage of 100, the 50% improvement calculation is (((1-([Previous FPY])) *.5) + [Previous FPY] ), this way we'll always get the improvement relative to the previous period and still under the 100% mark. Obviously, the higher the Previous FPY % is, the less room for improvement you can have in the next month's goal.

I've added an additional column in my spreadsheet to help show the difference between the hard calculation for the 50% improvement as well as the overriding calculation to show what the goal should be showing in Tableau based on maintaining the integrity of the new FPY goals at the time they are applicable. I've also added a comments column that depicts how/why this logic should be working for each month to help illustrate my desired results.

Thanks again Chris!

-Eric

• ###### 3. Re: Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

Hello,

This looks similar to what you want, but I am not sure this is how you want it to be done. I just used your calculation to get the 'Table Calc' percentage for each month and then did a running max table calculation based off the 'Table Calc' percentage.

1 of 1 people found this helpful
• ###### 4. Re: Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

Brent,

I believe this is indeed the answer! Obviously the spreadsheet example was simplified just to get some data out there, whereas my SQL connection to data has other calculations involved. But the important thing was just getting the right type of table calc, and I believe Running_Max will do the trick! Once I (hopefully) verify this to be true, I'll be sure to mark this as the correct answer

Thanks for your help on this, sir. It's much appreciated!

-Eric

• ###### 5. Re: Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

Brent,

Sure enough, this worked as it's setup in my workbook! You have no idea the background table calc coding that was going on to the level of assigning numeric values for each month based on it's relative performance from the month before and then doing several lookups to pull the previous month's goal forward. All I needed was one simple Running_Max calc

As usual, there's a simple solution using a calculation already there, thank you for showing me the light!