1 of 1 people found this helpful
It'd be clearer if you could share a packaged workbook with your data, but I believe that you are saying you are getting the "Years of Service Remaining" as a dimension for each employee in one source, and you have another source where "Future Service" corresponds to a value for such an employee's 401k.
You may be able to blend the data from your separate sources. Here is a resource for that.
http://kb.tableausoftware.com/articles/knowledgebase/relate-summarized-data-60 (may have to copy and paste link in new browser window)
As of Tableau 7, you can also use dashboard actions to filter across sources, as well as parameters. (8 has a bit more functionality around filters, but I haven't played with it much yet.) You could create an action where selecting an employee gives you "access" to the Years of Service Left dimension value which you would equate to Future Service on the other source.
Hope that helps a bit. If you need more assistance, could you post a packaged workbook?
Thank you for the help.
I have attached a copy of the workbook to my original message.
I can't figure out how to data blend here since there are no matching fields between the two views.
When you open up the workbook, you will see the 401(k) Calculations view.
Other than Retirement Year and Future Service, all the other fields on that view are calculated.
What I want to happen is the end-user will start with the Total Retirement PC DB.
On that dashboard, there are a host of parameters and filters that the end-user can use.
For example, the end-user filters to the individual with the Last Name Adams. At that point, the end-user sets the Retirement Age parameter to 70. The Years To Retirement calculated field now shows that Adams has 18 Years To Retirement. I want to use that 18 Year to Retirement value to lookup the 401(k) EOY Balance on the 401(k) Calculations view. In staying with this example, since Adams has 18 Years To Retirement, his 401(k) EOY Balance is $1,495,446 (the EOY value where Future Service equals 18).
I need to use that EOY value in other calculations for Adams.
Thank you for the help.
I am going crazy trying to figure this out.
1 of 1 people found this helpful
You currently have Future Service designated as a Measure, meaning Tableau wants to SUM/AVG/otherwise Aggregate the numeric values it finds in this field when representing it. Even though this field is numeric, its nature is that of a dimension. The quick test I always do in my head is "does it ever make sense to add these numbers together?" If not, I should drag it to the Dimensions pane...
So, in doing that, we're able to create the join and get to the record-level data from the first source to the 2nd. (You can only join on dimension fields.)
I'm a little pressed for time, so I can't get to the next level--it's not as simple as I was hoping because the value you want from the 401k source is a table calculation. That will be hairier.
I noticed you're using a SQL Server source--I bet it'd be easier to get the data you want all constructed into a single source using custom SQL. That's my 2 cents for now. If this is still plaguing you later, I'll try to circle back to it.
If you have a couple of minutes, I could really use your help.
I have really simplified what I was trying to do based on our suggestions.
I have uploaded a new workbook and to be honest, I think I am down to only one issue.
In the uploaded workbook, there is a worksheet called 401(k) Calculations.
Two of the calculated fields in that worksheet, 401(k) 50+ Catch Up and 401(k) EOY Balance use the PREVIOUS VALUE function.
Here is the formula for 401(k) EOY Balance:
IF ATTR([RIMS_Test_401k_Info (FCPA-Project)].[Future_Service]) > 1
ELSE [Initial 401(k) Catch-Up]
Using data blending per your suggestion, I have linked the two data sources that I am using by the field Years To Retirement. All of that is working fine.
One the worksheet, Sheet 13, I am laying out data from both data sources.
All the fields work properly except for the 401(k) 50+ Catch Up and 401(k) EOY Balance and I am sure that is't because I am using the PREVIOUS VALUE function. You can see that 401(k) 50+ Catch Up is coming up with a value of 1 instead of 7,216.
What I need is someway to pull the values from 401(k) 50+ Catch Up and 401(k) EOY Balance and not the formulas so I can use the data values in other worksheets.
Thank you for the help!
RIMS_Application_Upload.twbx.zip 129.7 KB
The workbook you attached appears to use a live SQL connection rather than an extracted data source.
Could you save as a packaged workbook and re-post?
Sorry about that Mark.
I uploaded the packaged workbook.
Well, after noodling with your workbook, I am not getting to any solution. Admittedly, I have a lot to learn about Tableau Calculations, especially from secondary sources.
I initially thought you were close enough to using a single source, that you would be able to drastically simplify this by removing the secondary data source and all the working with "aggregations only" that comes along with using multiple blended sources. I can re-create everything up to the 401(k) EOY Balance field using a single source. (see sheet named "401(4) calcs from main source"--they use the fields with "ALTERNATE" preceding the name.
But, technically, for someone who has 12 years to retirement (you were using LastName=Taylor), you need to access all 12 rows of the table displayed on the 401(k) sheet because your 401(k) EOY Balance table calculation field depends on the previous records for the whole table. I'm not sure where to go from here.
I don't want to lead you astray, so perhaps one of the Tableau masters will be able to assist you. Jonathan Drummey , Shawn Wallwork , Alex Kerin might be able to take it home. I've attached the workbook with my additions.
RIMS_Application_Upload.twbx.zip 195.5 KB
Some follow-up on this one...I did a screensharing session Matt and what we came up with is creating a scaffold data source that has a number of years in it and a "Blend Dimension" that has a value of 1. The original data source that has one row per customer also has the Blend Dimension in it. In Tableau v8 we use the scaffold data source as the primary then turn on the Blend Dimension as the linking field (which doesn't need to be in the view).
The data can then get filtered for the Customer from the secondary using v8's ability to filter dimensions without them having to be in the view. The computations then have enough years to run, and there's a final step to use a table calc filter to only return the proper retirement year for the Customer.
It's a little trickier with the aggregation because multiple customers can be selected at once, but that's the basic idea.