Tableau version 2021.3 included the features to create new rows. During this blog we will be using data from Preppin Data challenge Week 7 2024 by Jenny Martin:
Jenny Martin
When using real world data such as employee start and end dates, we may need to calculate how many times an employee has been paid. One of the best ways to do this on Tableau Prep is using the new row function. If we tried to complete this action using the date diff calculation, we would output a specific number which does not take into consideration the day of month for example.
When we select the new rows function on Tableau Prep, we will receive the option to add rows using values from one field or to add rows using value ranges from two fields. If we select the option to use one field, we will see the following pane:
We select the field we would like to use and can set the minimum start or end value. We can alter the increments of the new rows to either months or days. The values within our new rows can be null/ zero or copy from the previous row.
For the example specified (preppin data challenge week 7 2024), we will use the option to generate rows between two dates. The challenge requires us to calculate the number of Valentines Days each couple has spent together. We will generate rows between the date the relationship started and today.
This will then generate a row for each day between the day the couple’s relationship started and the today date (which was Valentine’s Day 2024). We can then filter the new rows field to include only rows where the month is February and the date is the 14th.
If we used the date diff calculation at a yearly level instead of the new rows function, the output number would not take into consideration the couples whose started their relationship before Valentines Day so have celebrated one more valentines day than the number of years they have been together.
