Tableau - data contains start / end date, how to make a fixed access for every month between?

1
votes

I have an extremely similar issue / set of data as the person from this post ..

Tableau month-based bar chart for data with date range

however the accepted solution doesn't realistically work for my case. They suggested making 12 calculated fields, one for each month, and pivot and display.

The 2 issues I run into with this, is my data spans across 6 years 1. So 6 years x 12 months would be 72+ calculated fields 2. Also, for each additional new month, I'd have to manually create a new calcualted field

My data looks like this:

name   | start      | end        
john   | 2015-01-15 | 2018-03-30 
tim    | 2016-02-01 | 2017-05-15 
brian  | 2017-02-15 | 2018-04-01

Is there a way in tableau, to have one of the axis show every single month from the min (start date) and max (end date), so that I can tally a total for each month?

UPDATE ** As far as a metric, it would just be a "Number of Records" count. Basically what I'm looking based on each persons start and end date, create a tally for EVERY month, showing how many employees we had for that specific month, so the output would look like (axis could be either way):

          Number of Employees
Jan 2015         3
Feb 2015         5
Mar 2015         6
  .              4
  .              5
  .              8
  .              7
Sep 2016         10
Oct 2016         13 
  .              12
  .              15 
  .              15
  .              15
Apr 2017         18
 etc...

Help or guidance would be greatly appreciated on this!

Thanks

2
tableau-api
Can you edit the question to include a mockup of what you want the output to look like? And your sample data doesn't include a metric, so what are you totaling? – Sam M
Thank you for the feedback, I just updated the question above with what I was looking to do, let me know if that makes sense, thanks again! – Jay
Two years ago I attended a class on time series analysis of temporal data that addressed this specifically. I don't have my notes handy but I'll see what I can dig up. Try a web search on temporal data tableau. – Sam M
Thank you I will look up temporal data tableau! I appreciate any notes or advice you can provide whenever you have the time, thanks again! – Jay
With a little help on what to search for, I came across a youtube video that gives me basically exaclty what I'm looking to do!! I will post it in the answers! – Jay

2 Answers

1
votes

With a little help fron @Sam M on what to search for, I came across a youtube video that basically gave me exactly what I was looking to do (the video does it by week, I just tweeked the formulas to month)

https://www.youtube.com/watch?v=gZ9p-hV6-qM

Thank you all!

1
votes

Another approach could be to pivot data and then use running sum to calculate the number of employees in each month.

Let's start with your sample data

Name    Start Date  End Date
John    2015-01-15  2018-03-30 
Tim     2016-02-01  2017-05-15 
Brian   2017-02-15  2018-04-01


Steps to follow in Tableau:

  • Pivot input data to have it in below format
Name  Pivot Field Names  Pivot Field Values
John  Start Date         2015-01-15
Tim   Start Date         2016-02-01 
Brian Start Date         2017-02-15
John  End Date           2018-03-30
Tim   End Date           2017-05-15
Brian End Date           2018-04-01
  • Create a calculated field Employees Count as
ZN(IF([Pivot Field Names]='Start Date') THEN [Number of Records] END) -
  ZN(IF([Pivot Field Names]='End Date') THEN [Number of Records] END)

  • Drag Pivot Field Values in "Columns" section. Right click it to select Month MMM YYYY format which converts it to continuous.

  • DragEmployees Count in Rows section. Right click it to select "Quick Table Calculation" as "Running Total" to have the computation using Table(across) and Voila!