Changing the Date pill to Continuous does result in this change for me:
Not the most attractive chart in the world--I'm thinking there is probably a better way to display this data. Not sure what method to suggest, though. 8.0 workbook attached.
Continuous Line ML.twbx 17.0 KB
Right, but with this view, the data is lying to us. My main concern is that the data look similar to how it is already represented, but the length of the lines between jobs relects the number of years it took them to transfer into that role.
Thanks so much!
Can you mock up what you want the view to look like in the end? I am not sure I understand what you are hoping to display.
I'm thinking this may be a scenario for a Gantt chart, but I don't have a lot of experience building them. Here's some linkage:
However, I believe you'd need to revise your data source to include a Start and End date for each role.
I tried to create an End Date for you using the formula:
DATE(YEAR([ROLE EFFECTIVE DATE])+[Years in role])
But this results in the year of 1905 for all dates. I'm not sure how to address that, but if I could create an End Date, I believe we could build your Gantt chart.
Here you go--check this out (click on the photos to enlarge them)
One thing to note, is the duration in years is shown as a whole number (integer)--the bars do not extend past a year value, even if the duration in that job was a decimal value. Perhaps someone like Jonathan Drummey or Shawn Wallwork could extend this to do that, if you need it. I could not figure out a way to use a Float/decimal data type in my work.
8.0 workbook attached. I can explain any of the work/calcs to you. I had to move things around a bit, and do some guess work, but it finally came out properly. I used a DATEADD calculated field to create the end date:
And for Job Duration in years (whole number), I originally created and used a DATEDIFF calc--this was a result of my experimenting. However, your Years in Role field could be used as well, and you could display that in the tooltip.
This was an excellent challenge for me--thank you for posting this question!
Tableau looks at the data type (integer or floating point) of continuous axes, and YEAR(ROLE EFFECTIVE DATE) is an integer.
Therefore, instead of YEAR(ROLE EFFECTIVE DATE) as the pill on Columns, create a calulated field with the formula FLOAT(YEAR([ROLE EFFECTIVE DATE])) and use that instead and you should see the bars be decimal. You may need to make sure the axis formatting is only showing whole integers, though.
Awesome! Yes, it did require formatting the axis, removing the comma separator, and decimal points. Once that was done, we get this:
New updated 8.0 workbook attached.
Sent from my iPhone
This looks great, guys! Thank you so much.
Matthew, I do have one question about how you were able to
get the duration in years. I created years in role with a calculated field in
Excel subtracting the differences between job start dates. What I need is a tableau calculation so that
when I update the extract the Years in role will update automatically.
Will your calculation for job duration fix that issue? What
is the calculation you used for job duration? I am unable to open your work
book, as I have Tableau 7.2. Also, I noticed a calculation in one of your
screen shots as end date 2, what is that exactly?
Thanks again, this has been so helpful!
Here is where I get stuck:
Don't worry about end date 2 (I was experimenting) , just use the end date calculation shown in the screenshots I posted above.
The "years in role" calculation is simply a DATEDIFF calculation for the two date fields, something like:
DATEDIFF('year', [StartDate], [EndDate])
This results in a whole number, not a decimal value. Good luck.