Part 2: The Timeline
Recap
In Part 1: Data Acquisition and Conditioning I explained the process of downloading data from Family Tree Maker (FTM) in csv format, and cleaning and enhancing the data in preparation for use in output sheets. This involved handling different date formats including dates prior to the twentieth century, duplicate names, configuration tables and filtering. Now it's time to dig into the timeline itself.
General Structure
The timeline reapplies some of the methods used in the Gantt Chart project. The basic structure is similar: names down column A as row headers, with birth and death dates (Gantt: start and end), and the columns as dynamic periods, responsive to selections and with a time-zoom option.
The body of the chart displays no data, only colored bars to show the lifespan of each individual. Columns can also be highlighted to show notable public events. With six header columns and six header rows, I'll need some screen management features to improve visibility.
The backbone of the chart is the list of names which will be a pivot table of the data table previously created, as the row headers. Column headers consist of periods and events. The chart body will be generated by a simple formula in each column.
Row Header Columns: The PivotTable
Pivot tables are commonly used for aggregation of pivoted data dimensions, but not here. This is a case of using the pivot purely for selection. This pivot has one filter field, Filter, and six row fields; Name, Surname, Line, Era, Birth, Death. For another example of a pivot used for selection , see the Keyboard Trainer project.
The PivotTable is simple to create. There are no column fields and no value fields. The filter field, "Filter" is set to "in". As explained in part 1, this removes names that deceased as infants or minors, or that are presumed deceased in that they lack death date but were born too long ago (parameter maxAge = 100) to be faithfully represented on the chart. This "in" cell will be covered by the slicers. The filter label can be hidden or covered as well. This filter is a permanent selection since the actual filtering is controlled within the data table.
Once the PivotTable is created, the goal is to make it and the chart table next to it look like one seamless table. Once the chart table is created and formatted, I'll work with both table formats to make that happen. It will take some tweaking to blend the two tables together.
To format the PivotTable, with the cursor in it, set Design → Report Layout → Show in Tabular Form (Alt,JYPT), and Repeat All Item Labels (Alt,JYPR). In Design → PivotTable Styles, select a suitable style, right-click → Duplicate. Then select the duplicated style, right-click → Modify as required. I used this to set borders for the Whole Table and to set fill and font colors for the Header Row. Finally, right-click → PivotTable Options... → Display → turn off Show expand/collapse buttons.
Column Header Rows
Periods
The two rows above the table header contain the dynamic period formulas. There are two input cells to the left of these rows, to set the bucket as decades or the default years, the other to set a start year. Row 5 provides the period relevant to the chart, row 4 groups row 5 in groups of ten. Thus, if row 5 is years, row 4 is decades, and if row 5 is decades, row 4 is centuries.
Other names are used in the formulas:
Left = relative cell to the left of the active cell
decades = $F$4="decades", where F4 is the first input cell
start = $F$5, the second input cell
$E:$E is the Birth column
Row 5 formula:
Row 4 formula:
Historical Events and Leaders
I compiled a table of notable events from various internet searches, and the leader in office at the time. These are mainly US national events and presidents. The intent is to give context to the lives of generations past. I used my own judgment to assign the most significant decade event and leader, in case decade columns are set. These two rows are simple lookups of the table.
Row 3:
Row 2:
The Chart Table
The chart table will butt right up against the pivot table. My favorite table style is "Blue, Table Style Light 9", white-on-blue header, no banding. This sets the standard that I will conform the pivot to.
The table has 200 columns for a reasonable compromise between timespan and performance. Every column is identical in content and format. The columns are named by numbers 1 to 200 and hidden. The table header row thus appears as a solid blue bar, neatly separating the sheet headers from the table body.
The chart table extends to the bottom of the pivot when its column filters are off ("Filter" is always "in"). When slicers are used, this will expose irrelevant chart table rows, which will be disregarded by formatting.
The Formula
The table body is filled with a formula to return the group number. Recall from Part 1 that the group was copied from FTM representing an ancestral trunk line. In the processing table it was converted to names, which are displayed in the PivotTable. Here in the chart table, it is converted back to numbers, in this case simply the MATCH number of the line name. I can't use structured referencing here, so I set up hybrid names to refer to both row and column headers. With the cursor in G7, Line = $C7, Birth = $E7, Death = $F7, and Year = G$5.
To step through the IF condition, I'm checking two things: birthdate <= this column's year, and death date >= this column's year. If both are true, then this column is within the lifespan of the person. If death is not found, then the condition ends at the current year, that is, the person is presumed living.
The next step in IF is when the condition is true, try to MATCH the line name to the groups table, and return the MATCH number. Outside the lifespan or in case MATCH is not found, return 0.
Recall that the lineal lines comprise seven cases: one for each grandparent and above (and all above's descendants) (4), one each for the father's relatives and the mother's relatives (2), and one for the immediate family (1). To these I added "na" for anyone not a blood relative. The formula thus returns numbers between 0 and 8 which are hidden from view by number format ";;;". Numbers 1 to 8 are then used in Conditional Formatting to set the fill color corresponding to FTM colors, except that group 8 is a gray color.
Conditional Formatting
With the formula returning group number 1 through 8, these are simple rules to set. Each group is assigned a color. Group 0 is blank, groups 1 to 4 are solid colors. Groups 5 to 7 are combined colors, using Fill Effects → Gradient of the two colors. Group 5 combines 1 and 2; group 6 combines 3 and 4; group 7 combines 1 and 3. Group 8 is gray.
The last two conditions use a light pattern to shade the column when there's an event or a leader to show, and the checkbox to show them is set.
Note that the lifespan rules have top and bottom borders only and are set to stop if true. This makes them solid bars on top of the vertical bars.
Slicers and Other Controls
Slicers
I'll add slicers for three fields, Surname, Line and Era. They'll remain in the default single-column vertical alignment. Since I already have a fairly tall header region, I'll size them to the same height, snapping them to the top of row 1 and the bottom of row 5. Widths will fit quite nicely into the B:E column range; Surname snapped to the left of column B, Era snapped to the right of column E, and in-between edges nudged (with snap turned off) for appropriate spacing.
Default sorting of slicers is not always good. In this earlier post (page 57 in the book) I discuss slicer sorting options. In Slicer Settings (with slicer selected, Alt,J,O,E), the default option is set to "Use Custom Lists when sorting". Normally I don't like this option, because it doesn't transfer easily to other users. I decided that's a small price here, so I set up a custom list for the Line field in advanced options, Alt,F,T,A. This also sorts the Surname slicer (with its own option set), putting the main trunk line surnames at the top. For the Era slicer, I just set descending sort, which puts "living" at top followed by "21st", "20th", etc.
The other details I attend to for slicers are in right-click → Size and Properties. I set "Disable resizing and moving" to avoid accidental nudging, and to remove the side and corner handles. I also set the property "Move and size with cells". I will have collapsible sections which I want to collapse the slicers as well as the grouped columns and rows.
Switches
I like to use stand-alone checkboxes as feature toggles, like light switches. I use Form type controls unless I really need ActiveX. Click here (page 68 in the book) and here (page 121) for more detail on controls. Here are the steps to set up a checkbox toggle:
Insert and position the checkbox (Alt,L,I,H)
Identify a cell to hold the checkbox state (e.g. a named cell on a config sheet)
Set the checkbox property to link to the cell (right-click→ Format Control→ Cell link)
Use the cell name as a format condition
It will be useful to be able to turn column highlighting on and off, so I add checkboxes linked to cells "showevents", "showleaders" and "showparty", tucked neatly into the corner of the respective header cells. This way, they don't even need a label because their purpose is inherent in their location. The linked cells are all on the config sheet. As shown in the image above, the highlight is activated when there is text and when the checkbox is set.
Screen Management
First, I set Freeze Panes with the cursor in the top left cell of the chart body, G7, to maintain header visibility as the chart is scrolled in either direction.
The two rows showing public events and leader names are quite tall to allow even brief text, rotated to preserve column width. The top six rows together can comprise nearly half of the screen height. To allow this section to be collapsed, the event and leader rows are grouped. Columns B:E are also grouped. Collapsing both groups maximizes the chart view.
Whenever using groups for screen management, I always open the outline settings (Alt,A,L) and uncheck the summary position options. This puts the expand/collapse buttons top and left of the groups instead of bottom and right.
To maximize the chart view,
Shift+Ctrl+F1 to set full screen mode
Collapse groups:
Set slicers as desired (they will disappear)
Data - collapse (Alt,A,H) (with A1 active)
Shift+Alt+scroll down (while hovering over A1)
Ctrl+8 to show the outline buttons and use them, then Ctrl+8 again to hide the outline buttons
To restore the default view,
Shift+Ctrl+F1 to cancel full screen mode
Expand groups:
Data - expand (Alt,A,J) (with A1 active)
Shift+Alt+scroll up (while hovering over A1)
Ctrl+8 to show the outline buttons and use them, then Ctrl+8 again to hide the outline buttons
To navigate the chart vertically,
Scroll mouse
Pg Up / Pg Dn
To navigate the chart horizontally,
Ctrl+Shift+scroll mouse
Alt+Pg Up / Pg Dn
Next
The next and final episode will introduce the "Generations Table". It has a single input cell with a drop-down list of names, 11 calculated columns and 510 rows listing ancestors for 8 generations. It uses binary logs and other interesting techniques.
Stay tuned!
Comments