Creating a Cyclic network chart
A few weeks ago we were asked at the Inviso office if it was possible to create cyclic network charts in Tableau. The answer was no and yes. Not by default, but we can “mess” around with Tableau and get there.
The case presented by the inquirer was from a process perspective, where he wanted to follow the routes/paths a sale followed through a sales channel. Questions such as which stations did the sale go though, what is the most commonly travelled path, which route is fasted/slowest, and at which stations are sales getting stuck and for how long were interesting to look at. All in all the good ol’ token and control points situation again. The viz I was trying to create was something similar to the one below:
The DataThe dataset that our curious inquirer sent us made somewhat sense, but missed 2 vital pieces of information before we could pull data into Tableau:
1) To and from points between the stations were missing.2) Location of each station.
As we have seen time and time again – Alteryx to the rescue! Here is an example of the initial dataset before any Alteryx magic had been applied:
In order to solve issue no. 1 I remembered a Tableau KB I’ve used a few times previously. In the initial excel sheet above we only have 1 row showing when a sale entered a station, where it went from and to and along with a from and to timestamp. In order to get our correct outcome we would need to split each row into two rows, the first showing our departure details and the second showing the arrival. We will make a unique key for each path, example and a path order so we can show how a sale jumps from each station to another. In other words each row needs a start and an end station, where the end stations is represented as the start station on the next row. See below where station 0 is the start station followed by station 5. Se example below and note how the StateKey, aka the station our sales process goes through, updates and are shown multiple times except for start and finish. For additional help I’ve identified the path in the field Concat_Path_ID which defines the route. The PathOrder field was likewise created in case I needed to play around with animations in the pages card.
Now that we have sorted out the structure we need, we still have to deal with our second issue – that is to assign each station with coordinates so that we can position them in a meaningful way. I knew that I had to plot this on a scatterplot to map it in a nice way, so I simply did some trial and error until I found a good match. I created the coordinates in a separate sheet, which I later joined on, based on the StateKey, to provide the correct coordinates. See coordinates below:
As I did not know if any business logic could be assigned to these coordinates I merely arranged them how I believed they looked visually appealing. Alternatively, if we would have known that e.g. station 1=accounting and 2=sales team ect., we could have mapped out the entire office floor.
The visualizationWith a nicely polished dataset, creating the visualization was actually quite easy as it merely consists of lines and circles merged together through our good friend “Dual axis”. The lat and long dimensions defined where on the scatterplot out our stations should be positioned and by using PathOrder on size, I tried to show that the thicker the line, the later in the path.
The finished workbook is presented below: