Creating Markov Chain Transition Matrix

A little question about Markov Chain came up lately. That pushed me to have a closer look at what it does. I had a vague idea about it before. This time I understand it better.

I saw a question in StackOverflow where people seems favoring a solution in SQL. I found it pretty easy to calculate in Tableau and with better visualization.

So, the matrix includes the probabilities of a group of entities transitioning from one state to the next state. The transition will happen within the same set of states.

Each row of the data set contains the old and new states per entity. We only need to calculate, given an old state, what is the probability of becoming a new state, or the distribution of probabilities of becoming any state. This article explains pretty well what the Markov Chain is.

So, I will use the data set in the above StackOverflow question as an example. Then I create the number of records and state-to-state probability in matrices.

Note that in the fractions, the denominators are calculated using row totals. The row totals can be computed using either Sum({Fixed Year13: Sum(Number of Records)}) or Window_Sum(Sum(Number of Records)) computing using Year14.

Further visualization is of the following varieties:

Click the above images to view or download the interactive versions.