Tableau vs. Power Pivot Part 9: Introductory Time Intelligence

Today, we will talk about utilizing Time Intelligence within both of these tools.  Time Intelligence is probably the most common category of KPIs.  Everybody wants to know how they compared against last year, last quarter, the last three months, etc.  For this reason, both of these tools have built-in functionality for these calculations.  However, we will limit this investigation to the introductory problems, as we are calling them.  In later posts, we will get into the more complex Time Intelligence situations.  As usual, we will use the Superstore Sales sample data set from Tableau.

Category 1: Three Month Moving Average

The moving average is arguably the most common time intelligence calculation.  Let's see how Power Pivot handles it.

Three Month Moving Average of Sales (Power Pivot)

We won't go into a long explanation of this procedure.  If you are interested, a colleague of ours, Javier Guillen, wrote a nice blog post about it here.  This calculated field leverages the time intelligence functions DATESBETWEEN(), FIRSTDATE(), LASTDATE(), and PARALLELPERIOD().  Unfortunately, there is no built-in function for moving averages in Power Pivot.  There's likely a way to get Excel to do this instead of using Power Pivot.  However, that would add another level of complexity to this calculation.  Also, it would be more difficult to get that data in Power View if you wanted to use it in a chart.  Alas, we have wandered.  Let's check out our final values.

Three Month Moving Average of Sales by Month (Power Pivot)

These values look good.  This calculation seems to work exactly how we wanted it to.  The major downside is the complexity of the calculation.  Next, let's check out Tableau.

Three Month Moving Average of Sales (Tableau)

This function is one of the many "Quick Table Calculations" within Tableau.  We reached this menu in four clicks, which makes this far easier than Power Pivot.  Also, this is a menu-driven process with no coding involved.  That's a huge plus in our book.  For more information about "Quick Table Calculations" check out the earlier post in this series, Tableau vs. Power Pivot Part 5: Point-and-Click KPIs.  Let's see how the output looks.

Three Month Moving Average of Sales by Month (Tableau)

These are the same numbers we saw in Power Pivot, albeit in 30 seconds as opposed to 5 minutes.  This is a no-brainer.

Winner: Tableau

Category 2: Year over Year Growth

Year over Year Growth, also known as "This Year vs. Last Year % Δ", is one of the other extremely common KPIs.  Let's see how Power Pivot handles it.

Year over Year Growth (Power Pivot)

This calculation is significantly less complex than the previous one.  It leverages the SAMEPERIODLASTYEAR() function which is designed to do exactly this.  The main thing that we didn't like was the fact that this calculation returned errors for the first year.  Therefore, we had to wrap in an IFERROR() function.  All in all, this was not a complex task.  Let's check out the results.

Year over Year Growth by Month, Year (Power Pivot)

These values look reasonable.  Next, let's compare it to Tableau.

Year over Year Growth (Tableau)

Once again, a Quick Table Calculation saves the day.  In fact, we didn't have to use any other menus this time.  All we did was click a few times and the answer popped right out at us.  Let's check out the results.

Year over Year Growth by Month, Year (Tableau)

These values are the same as the ones we got from Power Pivot.  This example is exactly like the last one.  Tableau edged out Power Pivot because of its easy-to-use Quick Table Calculations.

Winner: Tableau

Category 3: Three Month Forecast

This is a bit of a bonus category for us.  Forecasting is a newer feature that isn't present in many tools.  For quite some time, it's been a feature for the Mathematicians and Statisticians.  However, it's becoming more and more common for business users to try for themselves.  Let's see how Power Pivot can handle this.

Forecast (Power Pivot)

We could not find a built-in way for Power Pivot to do any forecasting.  However, Excel has some very powerful trend lines that can be used to forecast future values.  Our issue is that it requires a significant amount of statistical knowledge to create good forecasts using this system.  Another downside is that the range for this chart is hardcoded.  This makes updating the chart more complex.  Now, let's see how Tableau fares.

Forecast (Tableau)

Not only can Tableau's trend lines do pretty much everything that Excel's trend lines can do, except for forecasting, but Tableau 8 has a new forecasting algorithm that uses advanced statistical procedures to account for trend and seasonality.  This would not be a menu-based task in Excel.  Once again, this is an easy decision.

Winner: Tableau

Summary

Power Pivot has some very powerful Time Intelligence that allow the user to create virtually any calculation they want.  However, they do not have many built-in calculations for the more common calculations.  On the other hand, Tableau lacks the Time Intelligence functions, but has "Quick Table Calculations" which cover the majority of the KPIs you would find.  Also, Power Pivot was completely unable to offer a reusable forecasting algorithm.  Tableau's algorithm is in its early stages, but is still very useful.  The bottom line is this: If Tableau's Quick Table Calculations can handle your KPIs, then Power Pivot can't compete.  If your KPIs are more advanced than that, then perhaps Power Pivot's functions can help you out.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn
http://breaking-bi.blogspot.com