1 2 Previous Next 15 Replies Latest reply on Nov 16, 2017 12:10 PM by Tyler Garrett

    Actual vs Planned Headcount

    Doug Crow

      I have a Business Operations Dashboard in Tableau version 10.2.2  for tracking actual vs planned productivity.  Currently the data for the dashboard resides in an Excel & database updated by replacing the plan with the actuals every week.  I'm trying to replicate this dashboard and instead of using an Excel doc, create the same visuals with parameters to replicate the forecasted headcount and hours. 

       

      The calculation I'm struggling with is telling Tableau that if there is actual headcount in the database, than use that, however if there isn't (null), than you the parameter.

       

      So...the first calculation tells me how many people booked productive hours in a given month and is a string calculation.  (AWF Direct Headcount 2)

       

      I have a parameter for each month of the year for the planned headcount to plan forecasted productivity.

       

      The calculation "Monthly AWF Planned Headcount" says if the Month Name (String Calculation)="January" and ISNULL AWF Direct Headcount 2 THEN us the parameter (Float) the headcount for that month.

       

      The calculation is valid, however it doesn't recognize the actual headcount and doesn't recognize December.

       

      The calculation is on Sheet 23 of the attached packaged workbook.  The dashboard in the attached workbook reflects what I'm trying to visualize.

       

      What I expect to see, is Jan-Nov - It provides the actual headcount and December, it gives me the planned number in the Parameter.

        • 1. Re: Actual vs Planned Headcount
          Jim Dehner

          Hi Doug

           

          If you look closely at your calculation you have a logic error -

          each clause of your IF statement test to the Month and then checks to see if the value of head count is null and if BOTH of them are True it executes your THEN clause which it the Parameter value - But they do not return the d/b value when either of the expressions is not true  - you need and else clause - see below

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Actual vs Planned Headcount
            Doug Crow

            Hi Jim,

             

            Thank you very much for you swift reply.

             

            Okay…you got me there.  When I add the Else statement, I receive the following…

             

            Thanks again!!

             

            Doug

             

             

            1 of 1 people found this helpful
            • 3. Re: Actual vs Planned Headcount
              Jim Dehner

              here is your formula - you got that because all or your parameters are a type "Float"

               

               

               

               

               

               

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              1 of 1 people found this helpful
              • 4. Re: Actual vs Planned Headcount
                Doug Crow

                Hi Jim

                 

                Thank you so much!!

                 

                When I apply the calculation, December doesn’t show up?  With that calculation, any ideas why the December Parameter isn’t showing up?

                 

                Thanks again for all your help!!

                 

                Doug

                 

                 

                1 of 1 people found this helpful
                • 5. Re: Actual vs Planned Headcount
                  Jim Dehner

                  You don't have any December 2017 data do there is nothing to show

                   

                  Jim

                   

                  Please mark my responses correct to close the thread

                  • 6. Re: Actual vs Planned Headcount
                    Doug Crow

                    Hhmmm….

                     

                    Something still isn’t right…

                     

                    The calculation “Direct Headcount 2” reflects the accurate headcount.  Monthly AWF Planned Headcount isn’t reflecting the correct headcount.  It’s relecting the values in the parameters.  Did I not apply something correctly?

                     

                    The intention of the calculation is when there isn’t anything to display, such as December, that it will show the parameter value.  In this case, 125 (See parameter in the lower right corner of the pic below).

                     

                    Thank you again for all your time!

                     

                    Doug

                     

                     

                     

                     

                    • 7. Re: Actual vs Planned Headcount
                      Jim Dehner

                      Doug -

                       

                      Doug your formula for awf direct headcount 2 does not produce a number - it returns a the Name  - when you place it on the viz you are using Countd to aggregate - your Monthly formula is comparing parameters that are floating point numbers to the value to find nulls -

                      you have one of 2 ways to go here - either use you awf direct headcount to actually count the heads rather than returns names that you aggregate later

                       

                      or

                       

                      you do it in Monthly calculation - either way you are now creating an Aggregate level formula (which you need to do)

                       

                      The second way to do this is shown below - (not I only looked at January you will have to make the same changes to every clause

                       

                       

                      on a side note - you are brute forcing things with calculations that could be done with filters - you may have a reason for that but it is making you workbook more complex than it needs to be

                       

                      Jim

                       

                      also the training videos are really very good and can be found at Tableau Training and Tutorials

                      • 8. Re: Actual vs Planned Headcount
                        Doug Crow

                        Hi Jim,

                         

                        Thank you for your help.  My motto with Tableau has been, “harder than it has to be”  ☺  If there is an easier way, I’m all for it.

                         

                        The end result is to bring all of the labor types (AWF, Temp, Intern) together into one viz that reflects how many hours we produced compare to how many we planned/forecasted to produce.

                         

                        The headcount is just a means to an end.  Once I get the headcount figured out, I have to multiply that by the annual planned hours which each have a different effectivity.

                         

                        As you know, the trick I’m working on now, is how to “blend” the actuals using our database and the “Plan” using parameters in Tableu.

                         

                        So…Are you saying I don’t need the AWF Direct Headcount 2?  I can do everything I need to do with Monthly AWF Planned Headcount?

                         

                        I made the changed you recommended on your screen shot, (see below) however I’m still not seeing December.

                         

                        Thanks again.

                         

                        Doug

                         

                        Doug CROW

                        Senior Business Management Specialist

                        Wichita Site – AAEOS

                        AIRBUS

                        T  +001-316-299-0231

                        Doug.Crow@airbus.com<mailto:Doug.Crow@airbus.com>

                         

                        Airbus Americas Engineering, Inc.

                        1855 Innovation Blvd.

                        Wichita, KS 67208-2522

                        USA

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                        • 9. Re: Actual vs Planned Headcount
                          Jim Dehner

                          Doug

                          You are going well beyond the purpose of the forum - suggest you look for a Tableau consultant to work with on making your project go smoothly'

                          Jim

                          1 of 1 people found this helpful
                          • 10. Re: Actual vs Planned Headcount
                            Doug Crow

                            Understood.

                             

                            Thanks for all your help.

                             

                            Doug

                            1 of 1 people found this helpful
                            • 11. Re: Actual vs Planned Headcount
                              Jim Dehner

                              I don't want to leave you just high and dry

                              If I was working with a group on this I would do the following

                              1- determine how frequently the values in the parameters change to find a better way to get that data into tableau

                              2- work on Joining (not blending) data from your separate data sources

                              3- based on the audience try to do all the conditional checks in filters and not hard coded it into calculations

                               

                               

                              just some thought to think about

                              Jim

                              • 12. Re: Actual vs Planned Headcount
                                Tyler Garrett

                                From an extract perspective, I'd materialize a calc that does the string manipulation FIRST...

                                 

                                So, a calc like:

                                if month=jan then 1

                                elseif month=feb then 2

                                ....

                                Then OPTIMIZE the extract. SEE Optimize Extracts

                                Screen Shot 2017-11-16 at 1.39.16 PM.png

                                Now at a row level, above, the computer isn't hacking through the alphabet, special characters, and offers a normalized process utilizing INT() which are BUNCH faster than STRINGs.

                                 

                                Then implement this integer if statement.


                                What's nice about tableau is how it materializes the loop after you optimize, therefore removing the computation speed/process.

                                 

                                A database best practice is to REMOVE long strings, and generate NUMBERS!

                                 

                                Because of the fact it's only 0,1,2,3,4,5,6,7,8,9 possible... Where there are LOTS of characters, and the computer needs to sort through every letter, one at a time, and it's like a check list.

                                 

                                So, when building a fast workbook, you always translate this work into an integer scraping...

                                 

                                Otherwise you're asking it to do a string loop, and consider string character has...

                                let's just say it's 400 possibilities, per letter..

                                 

                                We can fake the math here:

                                400 * each character * each loop * each row of data... Wow, that's a big number.

                                 

                                Anything you can do to get away from using strings, in your conditional logic, will make this calc a lot faster.

                                 

                                What everyone has offered is brilliant, but it's not production ready, and will be slow.

                                 

                                And once this extract optimization clicks, Optimize Extracts - you'll be able to avoid generating these string loop calcs.

                                 

                                Again, make the first calc to translate STRING to INT. Which is basically copy pasting the code provided, and making the transition in the previous calculation. Because we want to off load hard work, to easy work, or Tableau will go slow -> when it should be going very FAST!

                                 

                                Enjoy!

                                 

                                PS: I've worked through at least 1000 workbooks, and all of them get hit with the exact same process....

                                 

                                Copy and paste every calculation into a spreadsheet,And next column, put the calculation name,

                                And then you can LOOK at everything, if you SEE ANYTHING that has a STRING lookup... Replace it.

                                Otherwise the workbook will continue to degrade as you add more complexities.

                                 

                                Best,

                                Tyler

                                Dev3lop

                                1 of 1 people found this helpful
                                • 13. Re: Actual vs Planned Headcount
                                  Doug Crow

                                  Thank you Jim.  I really appreciate it.

                                   

                                  I’ve been using Tableau for a couple of years and the more I know, the more we explore the more capability with find, hence the more sophisticated/integrated we want for our dashboards / visual mgmt..

                                   

                                  It’s continues to be a hard learning curve from using Excel and Powerpoint for 20 years.

                                   

                                  I’ve been watching the videos/training however, its difficult to find the video you need to match a particular issue your facing at the moment.

                                   

                                  Thanks again for all your help!

                                  • 14. Re: Actual vs Planned Headcount
                                    Doug Crow

                                    Thanks Tyler.

                                     

                                    We have the months identified in numbers.  Perhaps I should be using that instead of month name.

                                     

                                    Thanks again for all your help!

                                     

                                    Doug

                                    1 2 Previous Next