8 Replies Latest reply on Apr 30, 2013 1:05 PM by Jonathan Drummey

    Selecting values from a workbook based on a parameter calculation

    Matthew Petrich

      Hello everyone,

      I have a view in a workbook where the field values are all based on parameter input from the end-user (see attached image).

      Year of Retirement and Years of Future Service come in from a SQL Server table via a data connection but all the other fields are created based on input parameters from Tableau.

      I have another table in SQL Server that contains census information for employees.  One of those fields are Date of Birth.

      In Tableau I can calculate the employee's age and from an input parameter, I can see what age the employee would like to retire at.

      Based on the age of retirement and the employee's current age, I can determine how many years of service are left for this employee.

      For example, the employe is 58 as of today, he wants to retire at 65, so we have 7 years left of service.

      Based on the 7 years of service, his future 401(k) income potential is $343,854 (please see attached image where Future Service is 7 and 401(k) EOY Account Balance is $343,854).

      This is where my problem lies.  I can easily calculate Years of Service for the employee but I can't figure out how to use that value to "lookup" the 401(k) EOY Account Balance on the other view where not data connection exists between the two data sources.

      I was hoping that Tableau has some sort of lookup feature where I can create a calculated field where I tell Tableau to pull this value from this field based on another value from another field.  So I could pull 401(k) EOY Account Balance based on the employee having 7 years of future service left.

      I can't seem to figure out how to do that.

      Thank you for any help/insight you can provide.

      Matt Petrich

        • 1. Re: Selecting values from a workbook based on a parameter calculation
          Mark Holtz

          Hi Matthew,

           

          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?

          1 of 1 people found this helpful
          • 2. Re: Selecting values from a workbook based on a parameter calculation
            Matthew Petrich

            Hi Mark,

            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.

            Matt

            • 3. Re: Selecting values from a workbook based on a parameter calculation
              Mark Holtz

              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.

              1 of 1 people found this helpful
              • 4. Re: Selecting values from a workbook based on a parameter calculation
                Matthew Petrich

                Hi Mark,

                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

                THEN PREVIOUS_VALUE(1)*(1+[CPI])

                ELSE [Initial 401(k) Catch-Up]

                END

                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!

                Matt Petrich

                • 5. Re: Selecting values from a workbook based on a parameter calculation
                  Mark Holtz

                  Hi Matthew,

                   

                  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?

                  • 6. Re: Selecting values from a workbook based on a parameter calculation
                    Matthew Petrich

                    Sorry about that Mark.

                    I uploaded the packaged workbook.

                    Thank you

                    • 7. Re: Selecting values from a workbook based on a parameter calculation
                      Mark Holtz

                      Hello Matthew,

                       

                      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.

                      • 8. Re: Selecting values from a workbook based on a parameter calculation
                        Jonathan Drummey

                        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.

                         

                        Jonathan