When I study how to implement network graph in Tableau with this article, I wonder, when we face to the real data, for example, historical data of transactions where a database has each transaction record, how we can use this method. As we can imagine, data we will face will be much complex and have many records, so I felt that we need to know how to create it from a preparation process.
In this post, I try to make the network graph with more complicated data.
Here is google image search in case you are not familiar with the Network Graph.
Also this is the goal for this post: a small dashboard of transaction history data.
Just for a small example, with this Dashboard, we can identify who is receiving or sending a lot, which might be helpful for a decision maker like in a security department looking for money laundering.
(In case you are not R user, here is the data we will create.)
This post starts from creating sample data imitating transaction histories.
The data we will use have
・Transaction ID,
・Name_from: who made the transaction,
・Name_to: who received the money,
・Transaction: amount of the money of the transaction.
I used R for this creation. My code is here.
Name_from and Name_to are given as integer, and I eliminated some records where Name_from is the same as Name_to because all transactions should have done from a person to another person.
Then, for the visualization, let's sum up all transactions based on who send money to whom.
Then, it's also for the visualization, I add 'Nodename' and row ID. Nodename is for the circles on the network graph. You will see what it is for later, then we will get the main data for the data-viz.
By the way, we will use JOINER later so I name some columns in a certain way.
Notice that Nodename is created by "Name_from".
Then, because each transaction, for example, transaction A→B is between A and B, to create the viz, this transaction should be labeled with A and B. In other words, regardless it is from A or B, both node A and node B should have the transaction record. So we need dummy data where Nodename is "Name_to" and the data don't have any transactions.
Dummy data should look like this
We will use UNION with the dummy data.
Then we need to assign geometric info on each node. In this post I used sin() and cos() to make the nodes line along circle, but it's totally up to you.
Then for the purpose of visualizing who receives or sends most, let's calculate some numbers.
Well done, finally we've finished creating the data and preparing for the Network Graph.
And good news is that, 80% of time to create the data-viz is for data preparation. It's almost done.
So let's move to Tableau.
The first thing you have to do is JOIN and UNION. Set them in this way below, joining with 'Nodename'.
Then put everything like this. It's done, seriously.
Just to make it a bit easy to understand the columns, I named the names like this:
CASE [Name_from] WHEN 1 THEN 'Andy' WHEN 2 THEN 'Betty' WHEN 3 THEN 'Chris' WHEN 4 THEN 'David' WHEN 5 THEN 'Eric' WHEN 6 THEN 'Frola' WHEN 7 THEN 'Gina' WHEN 8 THEN 'Hendry' WHEN 9 THEN 'Iris' WHEN 10 THEN 'Jack' END
Name_to, Nodename can be done the same as well.
In conclusion, from our historical records, wee need to
・aggregate the data by who send to whom,
・create dummy data for UNION,
・create data of location and total transactions for JOINs.
I hope you enjoyed this post. See you.
Yoshi