The Multi-Row Formula Tool has a variety of uses. Within this blog I will be explaining how to use this tool to fill in missing gaps within a data field.
Every value within the Value column does not have a date associated to it.
Using a data set like this in Tableau would cause problems, each value would need to contain a date relevant to itself.
To be able to fill in each of the rows where there is no date we will use the “Multi Row Formula” Tool. You’ll find the Multi-Row Formula Tool within the Preparation tab.
Drag the tool in to your workspace and connect it to your data set. On the left you will see your configuration box appear.
At the top you will be given a choice to “Create a New Field” or to “Update Existing Field.”
For this part we will “Update Existing Field” for the Date column as we want to fill in the missing dates within this column.
For this particular task we will leave the rest of the configuration setting the same and now focus on the “Variables” and “Expression” sections.
This “Variables” section will make writing your “Expression” much easier. Here you will drag in which row you are trying to alter and based on another’s rows’ information.
Here you will drag in which row you are trying alter and select which row you would like to base these alterations on.
In order to fill in the missing dates (where Date is null), we will want to look at the date in the previous row (Row -1) and fill in the current row (+0-Active Row).
We can write this in the Expression Box as follows:
Expression Up close:
This calculation broken down:
Once you run your workflow including your new Multi-Row Formula Tool, the Date field in your data set should be filled in.