The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,766 other followers

Excel Pivot notes: Table, Pivot Formula, 2D, 3D charts and secondary axes.

Posted by jpluimers on 2019/10/04

Since I don’t do Excel visualisations often enough, I always forget the details on Pivot Charts, some links and tips below.

TL;DR

You can’t have enough axes

The tips below assume you can create a pivot table from an existing table (that already can contain formulas), then show you:

  • additional formulas in your original table can make life easier
  • formulas for pivot tables themselves (named “Calculated Fields”)

Problem at hand

Creating graphs out of up and down time durations over time, aggregated by day.

Ideas for correlations that might matter:

  1. linear over time during a few weeks
  2. by week and by day of week

Incoming data:

  • end-timestamp
  • state (down or up)
  • duration of that state

Calculations

First of all, I needed “day of month”, “day of week”, and “week number” so I could group by those. Based on Readable weekdays in Excel, you get formulas like these:

  • =DAY(B4)
  • =WEEKDAY(B4) and =TEXT(B4;"dddd")
  • =WEEKNUM(B4)

Then I needed to split the duration of the state in distinct up/down durations. So I made a few formulas:

  • =("Up", A4) to have a boolean for up/down
  • =("Up", A4) to have a boolean for up/down
  • =IF(D4;C4;0)to split the up duration from the state duration
  • =IF(NOT(D4);C4;0)to split the down duration from the state duration

A pivot table could aggregate total up and down durations, but I wanted a measure of up ratio, so I needed a formula inside the pivot table itself.

Following the steps at [WayBackCalculate values in a PivotTable Use different ways to calculate values in calculated fields in a PivotTable report in Excel 2010, I got to this one:

This aggregates nicely: drag it to the aggregates column, then change the aggregation to “Average”:

Putting it in a 3D Pivot Chart

The most tricky part was getting the 3-D rotation right, so lets start with that:

  • No “Right Angle Axes”
  • X = 35
  • Y = 20
  • Perspective = 5

Easier was to have both Weekday (numeric) and WeekdayText (text) in the legend: Excel automatically combines them and sorts them numerically.

That got me the below graph (from the top data): Average of UpRatio plotted against Row-axis WeekNumber and Column-axis Weekday – WeekdayText.

Secondary axes

Finally I wanted to see what happened linearly over time, especially wanting to see the number of up/down events during the day correlated to the up time ratio.

Putting that in a graph turned out wrong, as from the table on the right, I got this:

 

It is hard to correlate ratios being less than 1, to event counts being much larger. Luckily I found [WayBack] Add or remove a secondary axis in a chart:

When the values in a 2-D chart vary widely from data series to data series, or when you have mixed types of data, you can plot one or more data series on a secondary vertical (value) axis. You can also add a secondary horizontal (category) axis, which may be useful in an xy (scatter) chart or bubble chart.

The steps are easy:

  1. in the cart select the red series
  2. on that series, in the toolbar, select “Format Selection”
  3. move it to the “Secondary Axis”

–jeroen

Axe image credits (clock-wise from top-left):

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: