7 Replies Latest reply on Jan 23, 2014 11:29 AM by Aaron Clancy

    Chart Future Time Goals

    Ryan Knight

      Hi All,

       

      New user here. This might be a simple question, but I am struggling to understand how to organize my data such that I can plot goal lines for periods outside of the data I already have. I attached an example in Excel that shows roughly how the source data are organized and what I am looking to do. The situation is:

       

      I have data on applicants by date hired.

      I have targets for the number of hires we need to make over time (into the future).

      I want to plot a running total of hires to date and a goal line of hires needed over time on the same chart.

       

      Thanks in advance.

        • 1. Re: Chart Future Time Goals
          Aaron Clancy

          Let me know if that helps.

          Screen Shot 2014-01-22 at 3.36.41 PM.png

           

          I did some quick date stuff to get a continuous X axis so that Dual was possible.  Im sure other people have better approaches.  That was just a quick solution.

          1 of 1 people found this helpful
          • 2. Re: Chart Future Time Goals
            Ryan Knight

            Aaron -- This is great! I really appreciate the quick response.

             

            It looks like I will have to re-arrange the source data outside of Tableau, correct? The ideal solution would preserve the structure of the source data and do the calculations necessary to create the 'Excel Work' tab in Tableau.

             

            (The reasons I am interested in this are that we have lots of data, so performance is slow in Excel, and I will want to able to filter on Site).

            • 3. Re: Chart Future Time Goals
              Aaron Clancy

              I actually just realized the excel file had raw data.   Let me see what I can with the raw data.

              • 4. Re: Chart Future Time Goals
                Aaron Clancy

                quick question about the raw data.  I only see 2 hires total.  The excel calc is doing something not very intuitive.  Can you double check that this is what is intended?

                • 5. Re: Chart Future Time Goals
                  Aaron Clancy

                  Couple of things in order to make this scalable for your needs:

                  1. You'll have to get creative with Custom SQL due to the cardinality of the data.

                  2. You'll need a date table (1st day of each month) added because Tableau needs a place to put values where your data doesn't already have them. This is a real simple step that only takes a couple seconds to add.

                  example:

                  Screen Shot 2014-01-22 at 8.26.30 PM.png

                   

                  (If your data was in a source like Oracle you could avoid this step by dynamically generating a dates list from DUAL as it's own table)

                   

                  In regards to the Custom SQL:

                  This is a quick and dirty way to get all of the data into a single result set

                  SELECT [Dates$].[Date] AS [Date],

                    max([TimeGoals$].[Percent Hired]) AS [Percent Hired],

                    sum([Applicants$].[Hired]) AS [Hired],

                    MAX([Goals$].[Projected Hires Need]) as [Projected Hires Need],

                    [Applicants$].[Site] AS [Site]

                  FROM  (([Dates$]

                    LEFT JOIN [TimeGoals$] ON month([Dates$].[Date]) = month([TimeGoals$].[Date])

                  and year([Dates$].[Date]) = year([TimeGoals$].[Date])

                  )

                    LEFT JOIN [Applicants$] ON month([Dates$].[Date]) = month([Applicants$].[Applied Date])

                  and year([Dates$].[Date]) = year([Applicants$].[Applied Date])

                  )

                  LEFT JOIN [Goals$] ON [Goals$].[Site] = [Applicants$].[Site]

                  group by [Dates$].[Date],[Applicants$].[Site]

                   

                  (notice that there is a reference to the Dates table/sheet I created in the previous step)

                  (I JOINed on Applied Date for applicants but you can switch that to hire date if that was intended)

                  Once in Tableau your data will look like:

                  Screen Shot 2014-01-22 at 8.30.22 PM.png

                   

                  the Date field represents a point in time and the details associated to that point in time.  Where there is site detail you will see X number of repeating dates that will be filtered down to just one after insertion into Tableau.

                   

                  it appears hires are a running sum so you'll need to add that into Tableau.

                   

                  ....And percent hired is sort of a rolling state.  In the example above, May has no "percent hired" so it takes on the value of the previous month.

                  Screen Shot 2014-01-22 at 8.37.49 PM.png

                  The above is from the workbook I'm attaching.  There is a site selector that needs to have null selected so as to not filter out the Projected values that are not site specific (i.e. the Time Goals Multipliers) This could be alleviated with more custom SQL but I don't have time.

                  I have a "Months into The Future" parameter because I didn't see any detail in your data as to when the projection should stop.  The line graph is different than yours because my logic is a multiplier based off of a particular month and couldn't tell what exactly yours was doing.  You can just adjust the Tableau Calcs to be more along the lines of the logic that you are using.

                   

                  As long as the schema stays exactly the same you should be able to swap excel files without the Custom SQL breaking when new data needs to be added although these kinds of solutions warrant a more refined data management solution such as a relational database in order to ensure things like referential integrity and data quality.

                  • 6. Re: Chart Future Time Goals
                    Ryan Knight

                    Aaron, thank you so much! I am amazed that you took the time to explain this so clearly. In particular, it was very helpful that you went into detail on the why behind your actions.

                     

                    My high level take-away from this is that I shouldn't rely on Tableau to build non-trivial joined tables from a relational source, and should instead build the tables I want at the source level and import them into Tableau.

                     

                    My low level take-away is that I have some tinkering to do!

                     

                    Thanks again!

                    • 7. Re: Chart Future Time Goals
                      Aaron Clancy

                      No problem.

                      I think that once you get the hang of the data with respect to Tableau you'll be able to feel out what a best approach would be.  I use Tableau ALL the time for non-trivial joins but I'm also connecting to an actual relational database with the proper constraints in place so I'm confident that my SQL will continue to work throughout the data expansion lifecycle.  If you're comfortable enough with SQL you don't have to change your current data structure.