3 Replies Latest reply on Nov 16, 2015 8:51 AM by Nadia Garrett

    Line graph showing headcount using two dates

    Nadia Garrett

      Hi there,

       

      I'm creating a line graph which shows number of assignments over the period of Jan 2014 - Dec 2015.

       

      In the underlying data, I have a start date and an end date per assignment. E.g. Start date of 11/08/2015 - 12/24/2015. I want the line graph to count this as one assignment in the period in between, and then count it as 0 after 12/24/2015.

       

      I assume this is some sort of calculated field, but any help you can provide would be amazing. I have attached a sample workbook.

       

      Many thanks,

       

      Nadia

        • 1. Re: Line graph showing headcount using two dates
          Jonathan Drummey

          Hi Nadia,

           

          You're going to need to do some work to rearrange your data. Here's a post that covers the why: http://redheadedstepdata.io/lookup-vs-transactional/ and a list of posts that cover some different hows: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?.

           

          The data is set up with a record for each ID when what we need is a record for each ID & Month between the start & end dates. This post is offering a new solution from the above that takes advantage of Tableau's pivot feature to avoid some data prep. (However, it does need more table calcs, that might not be what you want). Here's how I built it:

           

          1) Duplicated your data source and pivoted it to create a "Date" column from pivot field values and "Start/End" column from pivot field names.

          2) Started a view with MY(Date) on Columns and Business & Unique Identifier on Rows.

          3) Right-clicked on the MY(Date) pill to turn on Show Missing Values to ensure that the dates are padded in. Tableau doesn't let us evaluate the padded Date as a regular dimension, so we have to go through a couple of steps to create a calculated field to be the Padded Date.

          4) Created 1st Date with the formula TOTAL(MIN([Date])) and put that on  Text Shelf with an Advanced... Compute using on all the dimensions in the view. This is a table calculation, for more information see http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations.

          5) Created Padded Date with the formula DATE(DATEADD('month', INDEX()-1, DATETRUNC('month', [1st Date]))), this has a *nested* compute using on the Date. Now we have the dates fully padded out across all the months.

          6) Created Counter with the formula

          IF [Padded Date] >= WINDOW_MIN(IF MIN([Start/End]) == "Start Date" THEN MIN([Date]) END)

            AND [Padded Date] <= WINDOW_MIN(IF MIN([Start/End]) == "End Date" THEN MIN([Date]) END) THEN

              1

          END

           

          This returns 1 if the padded date is between the start or end date. This all has a nested Compute Using on the Date.

           

          7) Created the # of Businesses calc with the formula IF FIRST()==0 THEN WINDOW_SUM([Counter]) END, this has a nested compute using on the Unique Identifier so it sums up all the Counter values for each month & Business.

           

          Here's the workout worksheet:

           

          2015-11-12 11_49_06-Tableau - Test workbook # assignments jtd.png

           

          8) Duplicate this sheet, drag off the 1st Date & Counter pills, turn off the tooltip for Unique Identifier, and put the Padded Date pill on the Filters Shelf to filter for 2015, and Ctrl+Drag a copy of the # of Businesses pill to the Filters Shelf to filter for Special->non-Null values:

           

          2015-11-12 11_52_15-Tableau - Test workbook # assignments jtd.png

           

           

          Workbook is attached, let me know if you have any questions!

           

          Jonathan

          • 2. Re: Line graph showing headcount using two dates
            Nadia Garrett

            Wow, that is a very comprehensive reply. Thank you so much! I'm going to have to read this several times over to fully digest...

            • 3. Re: Line graph showing headcount using two dates
              Nadia Garrett

              Hi Jonathan,

               

              Thanks again for your help on this - I've gone through it and managed to replicate it using my real data, which is great.

               

              I just had two questions as a follow up:

               

              1.) The ability to right click and view data is now not possible, I assume, because everything is padded in? Say for example, I wanted to right click and view the 16 people in Corporate in July 2015 - is that now not possible?

               

              2.) I want to use a date field as a quick filter slider on my dashboard, so that the user can slide between January 2014 and January 2015. I have added padded date as a quick filter to my view, but the slider becomes very compact with large gaps either side. Is there any way to instruct the filter to only include the range of 01/01/2014 - 31/12/2015 when I convert it to a slider? It seems like it should be a simple fix, but I've tried various iterations and no joy!

               

              padded date 2.PNG

              Many thanks again for all your help!

               

              Nadia