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:
- linear over time during a few weeks
- 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 [WayBack] Calculate 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
Read the rest of this entry »
The Wiert Corner - irregular stream of stuff 