skip to Main Content

Cycle Plots Show You Data Insights using Power Pivot

By Avi Singh [Twitter]

If you want to look for trends based on weekday (Sunday…Saturday) or month-of-the-year seasonality (January…December), Cycle Plots can be a potent visualization tool. Some clever folks thought of this back in 1978, but my education on cycle plots has been from Naomi Robbins’ excellent paper. This question was asked during one of our Q&A sessions for the Online Class (Next Class scheduled for Aug 3-4). In this post I’ll discuss the Cycle Plot and then we would build it step by step using Power Pivot. Here is the end result we will achieve (animated gif):

Cycle Plot showing Weekday values for each Week Number and the Average. A slicer also lets the user select the weekday chart should start at

Looking for Periodic Trends

We’ll use sample data showing eight weeks worth of web traffic and we are looking for trends based on the weekday (Sunday…Saturday).

Try 1: We might plot Visitors by Weekday. While this does show the overall pattern of visitors across weekdays, we have no information of how visitor count is changing over those eight weeks.

Try1: No information on how visitors trend changes over the eight weeks

Try 2: We can plot visitors by date. This shows a trend over time and the cyclical pattern is apparent. However, it is hard to track the performance of a single weekday, say Monday over the eight weeks.

Try 2: Hard to track a single weekday (say Monday) over the eight weeks

Try 3: We plot by weekday but add Weeknum as a series. This has a lot of information coded in the graph, but that is also the problem. Viewer is overwhelmed and it is hard to look for the patterns we want to see.

Try 3: A little too much information

Cycle Plot

A Cycle Plot would show data for each weekday broken by the Weeknum. With the same data now rendered as Cycle Plot, you can see the trend for each weekday and see them in relation with other weekdays. Insights just start hitting you on the head!

Cycle Plot: Click to enlarge, see numbered insights below

1) Friday is the peak traffic day and Mon/Tue are the low traffic days.
2) Thu shows strong growth, especially in the recent weeks
3) Fri shows steady growth
4) Sunday shows a gradual decline (except for last week) and may need closer monitoring

Building a Cycle Plot Using Power Pivot

Read the Rest