Recently, a client reached out to see if I could help re-order the categorical Y-axis of their gantt chart by the numerical value on the X-axis. The client wanted the Y- axis ordered by the date of the first occurrence of an event. As the chart descended on the Y-Axis, the values would get larger on the X-axis. To do so took some trickery and an outsmarting of Spotfire–the methods which I will share here. Solving the problem left me with two learned lessons:
- How to re-order a Gantt Chart’s (or any Scatter Plot’s) categorical Y-axis by the value on the X-axis.
- Bonus trick: how to “white-out” an axis label so that it doesn’t show, while other labels remain.
I have anonymized the data, but my client’s original Gantt Chart looked like this:
The Y-axis consisted of assets while the X-axis consisted of dates. The Gantt chart mapped four dates bookended by a start and end date for each asset. So, how to rank the assets so that the ones that started first are listed on top? We could manually change the sort order in Column Properties, but no one wants to go through that painstaking process. We’re a Data Science company, we automate!
My first step was to create a calculated column ranking the assets by date: DenseRank(First([Date]) over ([Asset Name])). Let’s break down that formula.
- “First” dictates the aggregation method: when more than one instance of a category occurs, the formula will take the first one.
- “Over” dictates that the category aggregated over is Asset Name. So, we have one (and only one) number representing each asset: the first date mentioned for the asset.
- “DenseRank” then ranks those assets by first date.
With my Date Rank calculated column on hand, I placed it next to Asset Name on the Y-axis.
Not quite there yet. Our ranking shows up on the Y-axis next to the Asset Name, but since we placed it to the right of the Asset Name, the Date Rank takes a back seat. But, if we click and drag the Date Rank to the left (or under) Asset Name on the Y Axis Selector…
That’s what we’re talking about. A nice cascade from the top left to the bottom right. Because we have placed Date Rank first on the Y Axis Selector, we have given it priority. We have taken a categorical column and given it an ordinal rank.
Whiting Out the Column Name
Still, I don’t like the way the names look on the Y-axis. Mainly, I don’t like how the Date Rank values sit next to the Asset Name, potentially confusing the viewer and also giving away all of our Spotfire secrets.
Unfortunately, Spotfire won’t let you keep one label on an axis but not another. What it will let you do is use an image as a label. So, if we get a white image, we can put that in as our label and it will appear the same as the background (if you are using the dark visual theme, you need to use a black image).
Click on the white image below to go to its page, then copy its URL. Trust us, there’s an image there.
Now, go to the visualization Properties > Y-axis > Label Rendering. Choose the Date Rank column from the Column window and Image from url in the Show as window,
then click Settings. Paste in your url.
Close, but not quite! I don’t like those tick marks to the left of Asset Name. Go to View > Visual Theme > Edit Custom Theme > Visualization scales. Select None for Tick marks.
And, there we have it! A Gantt Chart with the categorical Y-axis ordered by the X-axis values (with a bonus lesson in how to white out a label).
See if you can do it with your own data and let me know how it goes.
Jason is a Data Scientist at Ruths.ai with a master’s degree in Predictive Analytics and Data Science from Northwestern University. He has experience with a multitude of machine learning techniques such as Random Forest, Neural Nets, and Support Vector Machines. With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.