Tuesday, February 5, 2013

Creating a Period to Date Matrix in Tableau

Today, we will talk an idea I recently had for working with "Period to Date" values.  Tableau can easily create PTD values.  Here, I create daily, "Month to Date", and "Year to Date" values in a text table.  As usual, we will use the Superstore Sales sample data set in Tableau.
Period to Date Text Table
If I want to add more measures, I can add them to the columns.  However, the client wanted the table in the following format:
Period to Date Matrix Mock-Up
Here, the report date is always the previous day.  This means that we no longer have a time dimension in our graph.  Also, our measures are not on the same shelf as our table calculations.  How can we accomplish this?

Step 1:
  • Create the following calculated field
Period to Date Calculated Field
This field applies the label "Daily" to every record on the "Report Date", "Month to Date" to every record in the Month of "Report Date" (but not to those that already have the "Daily" label), and "Year to Date" to every record in the Year of "Report Date" (but not to those that already have the "Daily" or "Month to Date" labels).  A sample of this data, if the report date was 1/1/2012 would look like
Calculated Field Mock-Up
Step 2:
  • Add "Period to Date" to the "Columns" Shelf
  • Filter "NA" out of "Period to Date"
  • Add your measures to the "Text" Shelf
Period to Date Matrix
An astute reader might say, "Wait!!! Month to Date doesn't look at the complete month, and Year to Date doesn't look at the complete year."  You're right!  The "Month to Date" column is missing the "Daily" values and the "Year to Date" column is missing the "Daily" and "Month to Date" values.  This sounds like a running sum issue.

Step 3:
  • Change each measure to a running sum across the row
Period to Date Matrix
If we look back at the first picture in this post, we can see that the "MTD Sales" value at the end of January is $340,627, which matches our "MTD Sales" value here for the month of January.  This is evidence that our logic is correct.  Now, we've created the exact chart the client wanted and they have showered us with giant piles of money (I may or may not be exaggerating).

One issue with this method is, it requires that the measures be additive, or at least made up of additive components.  Feel free to play around with this method.  You can create a parameter or calculated field that would allow you to change the "Report Date" to your liking.  I hope you found this informative.  Thanks for reading.

EDIT: I found an issue with this methodology.  If the data date is the first day of the month, first month of the year, etc., you will not have "Month to Date" or "Year to Date" in your data set, respectively.  The workaround we used was to move the Period-To-Date function down into the SQL query and union hard-coded rows onto the data set for each missing value of the Period-To-Date function.

Brad Llewellyn
Associate Consultant
Mariner, LLC

1 comment:

  1. Hi Brad

    I read about this article

    Where dimension [Report Date] comes from?

    I tried use parameter date for [Report Date] but it still not have same result like what you do