Create % Difference Calculations for Rolling Date Range

Subscribe to our monthly newsletter to get the latest updates in your inbox

Tableau has an amazing ability to manipulate, calculate and present data. Calculated fields allow you to create custom calculations, compare fields, apply aggregations or apply many different functions on your data to create a custom dashboard for your reporting needs. As analysts, we often create summary dashboards highlighting KPIs on a rolling basis, such as last 7 days or last 30 days, that are dynamically populated. In addition, it is nice to have those metrics compared to the previous period in order to see how our KPIs are performing on a daily basis. However, Tableau does not provide this capability by default. In today's blog post, I would like to show you how to create a series of calculated fields to visualize last N days compared to the previous period.  

3 Calculated Fields You’ll Need to Setup

  1. Current Period - First, right-click in the worksheet data tab to create a calculated field for the current reporting period number which can be set as the last 7, 14, 30 or any number of rolling days you need.
    Then Write an 'IF' statement with 'DATEIFF' function that is equal or less the number of days in the current reporting period. T he big advantage of DATEDIFF is that you can specify what unit you want returned such as days, weeks, months, etc. In our example, we’ll use  last 7 days from today’s date.     This will give us the first piece of the puzzle, which is the number of pageviews for the last 7 days. Our next step is to create a calculated field for the previous period which in this example is 'Previous 7 days'. We will be able to accomplish this by using the same formula with a slight modification.  
  1. Previous Period - Create another calculated field and add 'IF' statement with the 'DATEIFF' function. However, since we want calculate the previous 7 days, we will add another 'DATEIFF' function in order to define that date range.
  1. % Difference - As a last step, create a calculated field and add the following calculation to find the % Difference.
    Now we're ready to use create calculated fields to create visualizations.

Visualization Example

Now that you’ve setup the calculated fields you’ll need, here are some examples of how you can use the fields to visualize and control data in Tableau reports.     Additionally, you can use these calculated fields to create and control indicator symbol which will be covered in the upcoming blog. Stay tuned for the upcoming blog post which will cover how to create calculated field and visualization to control indicator symbol. Please reach out to Analytics Pro to learn more about analytics and Tableau. Stay in touch on Twitter @AnalyticsPros and let us know if you have any questions!