Power View Tip: Scatter Chart over Time on the X-Axis and Play Axis
As you have seen in many Power View demos, you can run the Scatter Chart over time by placing date/time onto the Play Axis. This is pretty cool and it allows you to see trends over time on multiple dimensions. But how about if you want to see time also on the x-axis?
For example, let’s take the Hive Mobile Sample data as noted in my post: Connecting Power View to Hadoop on Azure. As noted in Office 2013 Power View, Bing Maps, Hive, and Hadoop on Azure … oh my!, you can quickly create Power View reports right out of Office 2013.
Scenario
In this scenario, I’d like to see the number of devices on the y-axis, date on the x-axis, broken out by device make. This can be easily achieved using a column bar chart.
Yet, if I wanted to add another dimension to this, such as the number of calls (QueryTime), the only way to do this without tiling is to use the Scatter Chart. Yet, this will not yield the results you may like seeing either.
It does have a Play Axis of Date, but while the y-axis has count of devices (count of ClientID), the x-axis is the count of QueryTime – it’s a pretty lackluster chart. Moving Count of QueryTime to the Bubble Size makes it more colorful but now all the data is stuck near the y-axis. When you click on the play-axis, the bubbles only move up and down the y-axis.
Date on X-Axis and Play Axis
So to solve the problem, the solution is to put the date on both the x-axis and the play axis. Yet, the x-axis only allows numeric values – i.e. you cannot put a date into it. So how do you around this limitation?
What you can do is create a new calculated column:
DaysToZero = -1*(max([date]) – [date])
What this does is to calculate the number of days differing between the max([date]) within the [date] column as noted below.
As you can see, the max([date]) is 7/30/2012 and the [DaysToZero] column has the value of datediff(dd, [Date], max([Date]))
Once you have created the [DaysToZero] column, you can then place this column onto the x-axis of your Scatter Chart. Below is the scatter chart configuration.
With this configuration, you can see events occur over time when running the play axis as noted in the screenshots below.
Enjoy!
About dennyglee
Very cool! Thanks for sharing.
Very cool, and will make viewing the data over time that much more interesting…
Pingback: Windows Azure and Cloud Computing Posts for 7/23/2012+ - Windows Azure Blog
Pingback: Consuming CANSIM Datasets using Data Explorer