Posted by Anselm Lopes
March 15, 2018
Recently while working with a client, I came across a challenging scenario: how do you filter the view, using a date filter, without impacting a calculation which included data that didn’t meet the date filter. In this case it was a moving average calculation (i.e., mean of specified time series).
This is a tricky situation because we can’t just date slice a part of the visual with conventional filter options. So for those of you brave enough to take it on, here’s the challenge:
Please follow the steps below to set up the challenge:
Step 1: Create a line chart from SuperStore Sales data, for Sales by Category using Order Date. Drill down to the quarter date value.
Step 2: Add a moving average quick table calculation and save it as the “Moving Average Sales” calculated field. If you don’t know how to “save” a quick table calculation, drag it from the view back into the side bar/data window.
Step 3: Create a user parameter that sets a whole number that represents the number of records(quarters) that should be included in the view.
In other words, create a parameter control where user can enter the number of quarters the moving average is calculated over.
Step 4: Reference the “Moving Average by” parameter in the “Moving Average Sales” calculated field you saved from the moving average quick table calculation. This makes the moving average computation dynamic for the number of quarters the user selects.
Step 5: Right click on “Moving Average by” parameter, a click on “Show Parameter Control”, and enter the number 6.In other words, show a six quarter period moving average.
You will see the following chart or values:
Notice the moving average values for Q1 of 2016. These values should not change when date filter is set to display 2016 Q1 as first value.
Step 6: Use a regular dimension date filter to limit the number of months or quarters. Try to do this without affecting the moving average calculation results.
To attempt this, let’s filter the view to show data from 2016 Q1. Once the filter is applied, the desired goal is for the moving average amount to remain unchanged.
Here is the catch. If we use the “Order Date” dimension as a filter to see data from January 1, 2016, we end up getting the following view:
You’ll notice here that Q1 2016 values are different than what we saw previously when no filter was applied. But we do not want the moving average to change. Even if the data is not present in the view, we should be able to see the exact same moving average values.
In our current view, the moving average calculation is not accurate for Q1 of 2016. This is because the moving average calculation is limited to the new date values available in the view after applying the date filter. In other words, the filter has been applied to the moving average source values. Hence, we only see actual values (sales) and not a true moving average for Q1 2016.
Ideally, we should have the following view:
So how do we achieve our desired result of limiting the date range without affecting the moving average? This is the Master Challenge. Give it a try and check your results against our solution which can be found here.