Using Parameters as a data Source for Student Number Planning
Covid-19 has presented the higher education industry with many challenges but arguably one of the most prescient right now is the issue of student number planning. It the best of times it would be a challenging undertaking to say the least but given the current climate of uncertainty it has become arguably even more crucial than ever.
The point of this blog post is not looking at how to build a student number planning model from scratch because that isn’t really Tableau’s forte. What is much more up Tableau’s alley is allowing users to connect to data an explore it to ask the questions that really matter. Being able to not only see the model presented in a visually striking way but also allowing the user to dynamically make changes to the forecast from within the dashboard and instantly see the effects this has on the number of students and expected funding;
To make this happen I used potentially one of my favourite techniques in Tableau, using parameters as a data source. In a nutshell this technique relies on me using a parameter action to turn a parameter into a list that captures the selections made by the user. I then use regular expression, another favourite of mine to bring this information back out again and use it to populate my graphs. This is just a high-level description of what’s happening behind the scenes, if you read on, I’ll explain what these techniques are if you’re unfamiliar with them, and how I used them to construct the dashboard.
As shown by the elements in the dashboard controls, I’ve set up the follow parameters for Student Type, School, Length of course, drop off % and forecast years. The useful thing about using parameters for this is that as placeholder values the user can specify what their values and then they can be passed into a calculated field.
The values for school and student type are updated using parameter actions so that the value in the list that the user clicks will then be the value of the respective parameter. The other parameters are updated in a more conventional way, using text entry fields and dropdown.
But now that we’ve got our user inputs, what do we actually want to do with them?
This is the calculation I use to update the Main Selection parameter when the button to add the students is clicked and it’s more or less the backbone of the dashboard. There’s a few things that it does so I’ll break it down in stages.
The first if statement checks if the Main Selection parameter contains the student type and school that the user has selected. If the parameter does already contain the school and student type, then a combination of regex functions are used to overwrite the existing entry.
regex replace – this looks for a specific value in a string and overwrites it. This is how the existing entry is overwritten.
regex extract – this looks for a specific pattern in a string and returns it. By nesting this in the replace statement, this is how I replace only the entry that matches the user’s current selection.
The entry is replaced by a string made up of the various user selected parameters, separated by underscores.
If the user has not made this selection of school and student type already then the selected parameters are concatenated together separated by an underscore and added to the main selection parameter.
The result is a string that looks something like this:
PGR HEU FTBiosciences50_1_7%_3 |
Now that with successive user entries made the Main Selection parameter has been populated, the next task is to extract that information in a way that allow the user’s entries to be added or subtracted from the underlying forecasting data. The end goal here is to have a new field which is a count of the students that have been added as a result of the user’s actions, and have this field split by school and student type.
As well as this we want to build in some business logic base on the data that we’re using: within each combination of school, student type and forecast year, there are different stages. The way to think about stages is to treat them like a student’s progress through their academic course. For example, a student in stage 1 of their course in Forecast Year 1 will be in stage 2 of their course in the next forecast year.
With this in mind, how the process of adding student’s will work is that when students are added is that whichever forecast year the user chooses as the starting year, students will be added to all successive forecast years based on the length of the course selected. In each successive forecast year, the stage of the added students will be incremented by 1 and the drop off percentage will be applied.
In simple terms this means that if 50 students are added to forecast year 1 for a given course, then in the first year there will be an additional 50 students in stage 1, then in the following forecast year 50 students will be added to stage 2 minus the drop off figure.
Extracting the data
The method for extracting the data uses Regex as I mentioned, and in practice how it works is by iteratively extracting the earliest part of the entry as a calculated field, and then using that calculated field in subsequent calculation to extract the next piece and so on until the entire entry has been extracted. Showing my calculations below will help to explain this in more detail.
On its own this calculation may look confusing, but as a field in a table it makes more sense.
Given the value of the list is – UG HEU FTBiosciences50_1_7%_3 |
What this calculation does is tell the regex to look in the parameter for student type and school. By enclosing school in brackets, I am telling the regex that this is the part of the string that I want returned. By putting this in a table what we can see is that for each row it is looking for that given combination of school and student type in the parameter, when the combination actually exists in the parameter it returns the school otherwise it returns a null.
Using this same method, I then extract all the other information in sequence. For example, my formula to extract the number of students to add looks like this:
The next step is to construct an if statement that builds in the business logic that I mentioned earlier so that the end result is as shown below. The new extract field displays the correct number of students to be added against a school and student type, factoring in the drop off percentage and the relevant stages.
The final piece then is to work out the recalculated forecast and using the New extract field this is simply a case of adding new extract to the existing student number to work out the revised count. Multiplying the revised count by the relevant fee amount then provides the recalculated fee amount as well.
So in a nutshell this was how the dashboard was put together, I’ve left out some of the formatting steps but with the information I’ve provided here it should be feasible to replicate the steps and achieve a similar result. It’s worth noting though, that no solution is ever perfect and this one is no different. The main caveat with this for example is the complexity of the calculations that sit behind the dashboard. While I’ve done my best to make sure that all the calculations are commented, it still takes a bit of time to get your head around them; for me they were the results of lots of iteration and trial and error. If something like this was handed over to someone to maintain then their ability to do so would depend heavily on their understanding of Regex and Parameters. As with all things though it’s worth assessing what your needs are and then deciding whether or not the solution you go for is going to be a help or a hindrance in the long run.
The interactive Tableau Viz can be found here: https://tinyurl.com/yy6s2qzv