Ok, so I’ve built this fantastic chart showing my sales for each month. It lets me easily see a trend in sale spikes around November.

But what if whoever’s viewing this chart needs to see sales on individual days – maybe they need to highlight certain days based on some criterion like the day’s profit ratio. Surely, we’d be able to do that easily without losing any important information, right?

Let’s take a look at viewing sales by individual day…

Ok, now I see who lot of detail, but it becomes somewhat harder to say with confidence that a trend exists and how strong that trend it. If I was to present this to a client, I’m not going to ask them to squint their eyes to see some hidden insight I think is hiding in there!

So how do we fix this? We use moving calculations!

Moving Calculations

All moving calculations do is essentially compute some calculation, such as an average of sales, over a window along the axis. In the chart above, for instance, instead of showing the sum of sales for each point of time along the x axis, we can show the average sum of sales over a number of days for each point of time.

Now, this isn’t being fancy for the sake of being fancy. The real benefit of this calculation is that is can allow us to actually smooth the line and reveal data trends over broader windows of time. But more on that later – first, let’s create out moving calculation.

Step 1

Right-click on Sales, then select Add Table Calculation. For Calculation Type, choose the Moving Calculation option. If the x axis is the time axis, we can leave Compute Using to ‘Table Across’ – this ensures the calculation is carried out on a window of time.

Once that’s done, we notice the chart trend has changed slightly. By default, the calculation is covering the previous 2 days, so we’re not expecting a big change.

What happens when the calculation is averaging sales over…30 days?

Step 2

Let’s make it easy for the user to change the window of days we’re calculating our sales average over.

Frist, create a parameter called Moving Average Periods as follows:

Now, add the parameter to the chart view and make it a slider type. Now let’s make sure our window calculation on sales reflects the window we’re specifying with our parameter.

The Sales measure in Rows should have a Table Calculation arrow indicator on it:

Double-click on it to view its actual calculation, which will give us the following:

We can see exactly what’s going on here and where the calculation is taking the previous 2 days. All we need to do is replace the -2 with the parameter. Also, don’t forget to put ‘-‘ in front of the parameter name to indicate that we’re looking at that many days in the past to the current day. Second, let’s change the 0 at the end to -1 so that we look at a window up to the day before the current day.

The final calculation should look something like this:

If we simply change the window size in the parameter slider up to 30, things become a lot more clear.

This is much better! We can much more easily see the trends occurring around November and July contrasted against one another.

And that’s all there is to it! Moving calculations used on a chart with a very fine level of granularity (days) to help see trends hidden in all the noise.

This is a powerful tool if we want to combine it with other marks. Below, I’ve created a version that indicates (using shapes) days where the profit ratio is above 20%.

Notice that even though we use moving calculations to find peaks and valleys in sales, adding in profit ratio uncovers an interesting find – not all high-sales periods are as profitable as low-sales periods. In fact, some of them are proportionally less profitable!

See what you can uncover using moving calculations.


The Data School
Author: The Data School