2021: Week 26 - Solution

 

Solution by Tom Prowse and you can download the workflow here. 

This week we looked at how to create rolling/moving calculations in Tableau Prep rather than when trying to calculate these when visualising the data. We looked at Prep Air destinations and what revenue we had made on a rolling week basis.

Step 1 - Start & End Dates

First we need to setup our data set so that we have 7 rows per date, or in other words, a row for each of the days that we are going to be averaging. Therefore, we need to calculate the 3 days before and after each of our dates with the following calculations: 

End of Week

DATE(DATEADD('day',3,[Date]))

Start of Week

DATE(DATEADD('day',-3,[Date]))

We now have three date columns for the start, middle and end of the weeks. 

Now we have the range of dates that we want to look across, we need to fill in the remain rows between the start and end dates. To do this we can make use of the >= & <= join conditions that Tableau Prep will allow us to do by joining our original data set onto our current table:

This will fill in the missing rows between our start and end date so our table should now look like this:

The final part of this step is to tidy some of the fields with the following steps. This isn't necessary but it's always good practice to remove unwanted fields and to rename fields so that they have useful and understandable headings.  

- Remove Destination-1, Start of Week, End of Week, Date-1, Revenue

- Rename Revenue-1 to Week Revenue to be summed 

- Duplicate Week Revenue to be summed (this is because we need to use this field twice in an aggregation tool later in the workflow)

Our table will now look like this: 

Step 2 - Calculate Rolling Total & Average

Now we have prepared our dates so that we have padded out the days that are needed for our weekly rolling total & average, we can now start to aggregate.

First we want to find the rolling total & average for each destination so we set up the aggregation tool like the following: 

We have also renamed the aggregated fields to Rolling Week Avg/Total so that makes it easier to understand.

We can also repeat the process on a separate branch to calculate the same but across all destinations, this will be the same set up but without the Destination in the Group By:

After this aggregation we want to create a 'Destination' field so that it matches our original table. In this case we are going to create a calculated fielded called Destination, with the string 'All'.

Step 3 - Union Results

The final step is to union both tables together. We can use the union tool as they have the same table structure and we want to 'stack' each table on top of each other.

After the union we are ready to output our data that looks like this: 

Here's our full output for comparison. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!