**VetPop2004 Tutorial**

**May 2007**

**Table of Contents**

How to Access the Reports

Selecting the Appropriate Reports: State and National Level Reports

##### Accessing County Data

Modifying Pivot Tables

Examples

Using PivotChart

Using the General Features of Pivot Tables

Saving the Results

**Contact: Mike Wells**

**Office of Policy and Planning**

**Department of Veteran Affairs**

**(202) 273 - 5106**

**Michael.Wells@mail.va.gov**

**[How to Access the Reports]{.underline}**

The Department of Veterans Affairs (VA) provides the official estimates
of the veteran population using the Veteran Population Model (VetPop).
The current version, VetPop2004, contains estimates of the number of
veterans classified by selected characteristics for the time period
between April 1, 2000, and September 30, 2033. To access the VetPop2004
state and national tables on the **internet**, go to
<http://www1.va.gov/vetdata/>, click on Demographics, and then click on
the GO button next to VetPop2004 Version 1.0 or click on the Estimates
and Projections under the heading State and National. Tables for county
and other small geographic areas are also available.

**Microsoft Excel**

VetPop reports are presented in special Excel files known as
PivotTables. Microsoft Excel describes a PivotTable report as "an
interactive table that quickly combines and compares large amounts of
data. You can rotate its rows and columns to see different summaries of
the source data, and you can display the details for areas of interest.
You can use this report to analyze the data, make comparisons, detect
patterns and relationships, and discover trends." If you need more
detailed information than shown in the examples of this document, click
on the following link to learn more about PivotTables.

<http://office.microsoft.com/en-us/assistance/HA010346321033.aspx>

Users who do not have Excel on their computers will not be able to view
the Excel pivot tables. However, an Excel Viewer is available free of
charge from Microsoft. Excel Viewer will enable you to open, view, and
print Excel workbooks, but not to modify the default layout for the
report. Click the following link to download this application, with
accompanying documentation:

<http://office.microsoft.com/en-us/assistance/HA011620741033.aspx>

###### Selecting the Appropriate Report: State and National Level Reports

The VetPop2004 Version 1.0 State and National Tables consist of 16
reports, classified by 4 types of estimates, 6 variable groupings, and
geography. The 4 types of estimates are:

-   L Tables: the number of veterans living at specific points in time,

-   D Tables: deaths over specific periods of time,

-   S Tables: separations over specific periods of time, and

-   M Tables: net migration over specific periods of time.

  ------- ---------------------- --------------------------- -------- -------- -------------- -----------
  ROW     COLUMN                 PAGES                       LIVING   DEATHS   SEPAR-ATIONS   Net
                                                                                              Migration

  State   **Age (5-yr)**         Year, Gender, Period of     1L       1D       1S             1M
                                 Service                                                      

  State   **Period of            Year, Gender, Age (5-yr)    2L       2D       2S             2M
          Service\***                                                                         

  State   **Race/Ethnicity**     Year, Gender, Age (5-yr)    4L                                

  Year    **Race/Ethnicity**     Age (1-yr), Gender, Period  5L                                
                                 of Service                                                   

  Year    **Branch of Service**  Age (1-yr), Gender, Period  8L       8D       8S              
                                 of Service                                                   

  Year    **Officer/Enlisted**   Age (1-yr), Gender, Period  9L       9D       9S              
                                 of Service                                                   
  ------- ---------------------- --------------------------- -------- -------- -------------- -----------

> \* Differs from the \"Period of Service\" variable appearing in all
> other reports in that it contains subtotals of certain periods.

The 6 groupings are based on the following primary variables:

-   1 for age,

-   2 for period of service,

-   4 and 5 for race,

-   8 for branch of service, and

-   9 for officer / enlisted status.

Finally, different levels of geography are available:

-   National (Tables 5 through 9),

-   State (Tables 1 through 4), and

-   County / VISN / Congressional District (available separately).

In order to keep the size of the state data sets (tables 1 through 4)
small enough to manage, and the cells large enough to have statistical
credibility, data in these tables were combined into 5-year age ranges.
Set 1 and Set 2 provide exactly the same data except that, in Set 2,
Period of Service is shown in columns with subtotals which aggregate
veterans who have served in multiple periods of service.

**[Accessing the County Data]{.underline}**

The county data (both living and deaths) are also presented using Excel
PivotTables, as well as on the internet,
[[http://www1.va.gov/vetdata/page.cfm?pg=2]{.underline}](http://www1.va.gov/vetdata/page.cfm?pg=2).
It can be accessed by selecting the appropriate category (VISN, STATE,
or Congressional District) under the "County Estimates and Projections"
heading. The county data are provided by: 4 Age groups, Gender, and Year
(4/1/2000 - 9/30/2030).

To access the living veteran population of Los Angeles (LA) county in
California as of 9/30/2006:

1)  Click the STATE,

2)  Click the down arrow button next to the State (Cell B5)

3)  Select California.

Or

1)  Click the VISN

2)  Click the down arrow button next to the VISN ((B3)

3)  Select VISN 22, where LA County is included.

LA County has an estimated 396,096 veterans as of 9/30/2006. These
county PivotTables can be manipulated to get different data or can
create charts in ways similar to theses tables in VetPop2004.

**[Modifying Pivot Tables]{.underline}**

The reference date for a given table depends on the type of estimate.
The L Tables are based on a point in time -- September 30 of each year.
The D, S and M Tables, however, represent the number of deaths or
separations, for example, over the entire fiscal year. This reference
date is found in cell B1 (first of the page variables) in the state
tables, and in Column A (first of the column variables) of the national
tables.

+-----------+----------------------------------------------------------+
| **Page    | **Title**                                                |
| va        |                                                          |
| riables** |                                                          |
+-----------+----------------------------------------------------------+
|           | **Row variables**                                        |
+-----------+----------------------------------------------------------+
| *         | Data                                                     |
| *Column** |                                                          |
|           |                                                          |
| **va      |                                                          |
| riables** |                                                          |
+-----------+----------------------------------------------------------+

By clicking on the drop down arrow next to the variable, you can change
the value. Often, the value is set to ALL. A simple change might be to
select Gender=Male to see the numbers reflect the data for Males only.

**Examples**

To illustrate the power of the reporting capability of VetPop2004, we
demonstrate three examples. The user is invited to duplicate these.

Note: The changes made to the tables cannot be written over the original
tables because they are read only documents.

***[Example 1]{.underline}:** Calculate the percentage change in the
number of living veterans in Florida from 9/30/00 to 9/30/2010.*

**[Steps]{.underline}**:

1.  What are the requirements to calculate the rate change between 2000
    and 2010? We need data describing living veterans, state, and years.
    The one that has this information is *Table 1L*.

2.  Select 1L from the VetPop2004 Version 1.0 State and National Tables.
    (http://vaww1.va.gov/vetdata/docs/VP2004B.htm)

3.  Click button ![](media/image1.png){width="0.4375in"
    height="0.1875in"} from the table of contents.

4.  Click the down arrow button next to the Date (cell B1) and select
    9/30/2000. Florida's estimated veteran population was 1,861,780 as
    of 9/30/2000.

5.  Repeat step 7 and select 9/30/2010; Florida's projected veteran
    population as of 9/30/2010 was 1,642,404.

6.  Calculate the percentage decrease \[(1,861,780 --
    1,642,404)/1,861,780\] = 11.8%.

7.  Use the Back button to return to the main table.

# ***[Example 2]{.underline}**: Compare the death rates for the veteran population in Florida and the total veteran population in Fiscal Year 2001 (FY 2001).*

**[Steps]{.underline}**:

1.  Requirements: deaths, state, and FY 2001.

2.  Open *Table 1D*, by selecting the
    button![](media/image2.png){width="0.4270833333333333in"
    height="0.16666666666666666in"}.

3.  We will need to modify the default report for *Table 1D* by pivoting
    the Fiscal Year to the column position. To do this, place the cursor
    anywhere in the Pivot Table. Then, on the Data menu in the toolbar,
    click "PivotTable and PivotChart Report" and then select "Layout".
    Or just RIGHT-click any cell in the PivotTable, select the "Pivot
    Table Wizard" option, and then click "Layout".

4.  Drag the "Fiscal Year" button from the Page position to the Column
    position, and drag "Age Group" button from the Column to Page
    position. Click "OK", and then click"Finish".

5.  Record deaths in FY 2001 for the state of Florida (55,775). The
    estimated number of total veteran deaths in FY 2001 is 664,057.

6.  Repeat steps from Example 1 to get the relevant denominators. As of
    9/30/2000 there are 1,861,780 living veterans in Florida, and
    26,542,460 total living veterans.

7.  The state of Florida crude death rate was 2.9% and the national
    veteran population crude death rate was 2.5% in FY 2001. Florida has
    a slightly higher crude death rate than national in FY 2001.

8.  Use the Back button to return to the main table.

***[Example]{.underline} 3*:** *Calculate the rate of increase for
living veterans age 85 and older in Florida from 9/30/2000 to
9/30/2010.*

**[Steps]{.underline}**:

1.  Requirements: State, Age Group 85 and older, and Year.

2.  Open Table 1L by selecting the
    button![](media/image1.png){width="0.4375in" height="0.1875in"}.

3.  We will use the Group function and Hide Details function to obtain
    the sum of all those veterans age 85 and older.

-   First, select cells P6 and Q6 (the two age groups: 85-89, and 90+)

-   Holding the cursor over the selected area, click the right mouse
    button.

-   Select the "Group and Show Detail" feature, and then select "Group".

-   A new Group1 have been created. Rename "Group1" to "85+".

-   Click on the newly created "85+" age group cell.

-   Click the right mouse button, choose "Group and Show Detail" again
    and then click "Hide Detail". The two selected age groups are
    summed, and formatted in one column.

4.  Click the down arrow button next to the Date (Cell B1) and select
    9/30/2000. Record the projected veteran population age 85 and older
    for Florida for 9/30/2000 (52,763). Then change Cell B1 to
    9/30/2010, and record the projected veteran population age 85 and
    older for Florida for 9/30/2010 (128,111).

5.  The VetPop2004 projected 1.5-fold increase in the number of 85 and
    older veterans in Florida from 2000 to 2010.

####### **Using PivotChart**

Looking at the last example, perhaps you are interested in creating a
chart (or graph) of the number of veterans 85 and older by state.
Continuing with the previously opened file, *Table 1L*, select the chart
icon ![](media/image3.png){width="0.2604166666666667in" height="0.25in"}
on the PivotTable toolbar. This action creates Pivot Charts. If the
PivotTable toolbar is not visible, click on "View", "Toolbars", and
check "PivotTable". A PivotChart can produce a large number of charts,
one for each combination of the pivot variables: period of service, age,
gender, state, and year. However, the initial chart displayed reflects
the entire complexity of the underlying table. For this example,
hundreds of data points are displayed, just as hundreds of data points
are shown in the Excel PivotTable. In fact, the amount of data displayed
renders the chart incomprehensible. To simplify this chart, deselect all
age groups other than the 85+ group in the pull down menu for the
grouped age categories (see "Age Groups2"). The chart should now be
clearer. With this quick adjustment you should have created a clear
chart showing which states have the greatest number of older veterans.

# 

# **[Using the General Features of PivotTables]{.underline}**

Some of the many ways to change the default report (table) are the
following:

1.  An [alternative page]{.underline} can be selected by using the drop
    down menus in the cells at the top of column B. Click on the arrow
    in one of the cells and a selection list will appear. Click on your
    selection (and in Excel 2000 click OK) and a new tabulation will
    appear restricted to the selected group.

2.  [Rows (or columns) can be combined]{.underline}. Hold the \"Ctrl\"
    key and use the mouse to select the rows by clicking on the row
    label cells to combine (release "Ctrl"). Right click and then select
    \"Group and Outline\" on the pop-up menu and then click \"Group\" on
    the sub-menu. Make sure the cursor is within the selected rows. As
    before, right click and select Group and Outline. Now click \"Hide
    Detail.\" The selected rows will be replaced by a single row
    containing the sum of the originally rows. The combined row will
    have the heading \"Group N\" where N is an integer. It can be
    replaced with more meaningful text. The columns in Tables 7L through
    11L, 8D, 8S, 9D, and 9S and the County Tables cannot be combined in
    this way.

3.  [Variables can be interchanged]{.underline} between rows, columns,
    and pages and [more than one variable]{.underline} can be put into a
    row or column. This can be done by dragging variable names from one
    place to another on the spreadsheet itself. But it is easier to do
    it by using Step 3 of the PivotTable Wizard (and in Excel 2000 and
    2002 the "Layout" option). One way to get to the Wizard is to place
    the cursor within the table and right click. Then click Wizard on
    the menu that appears. (One can also get to the Wizard from the
    PivotTable toolbar, or from the Data menu on the Menu Bar.)

> There are certain restrictions. The column variable in Tables 2 cannot
> be moved to a page variable because it has subtotals; it can be moved
> to a row variable. The column variables in Tables 7L through 11L, 8D,
> 8S, 9D, and 9S and the County Tables cannot be moved to page variables
> because they are "data". For the same reason they can't be moved to
> rows using the Wizard. Many users will not be able to have more than
> two variables in either the row or column position due to memory
> limitations of your particular computer. It is also inadvisable for
> some users modifying PivotTables using the Wizard to select the New
> Worksheet option, which requires more memory than selecting the
> Existing Worksheet option.

4.  [Deleting or changing rows, columns, or cells cannot be
    done]{.underline} the way it is usually done in Excel. In Excel 2000
    deleting a row or column can be done using the drop down menu
    obtained by clicking the arrow in the cell with the name of the
    variable. For rows, it is at the head of the column of row labels;
    for columns, it is at the left end of the row of column labels. In
    Excel 97, it can be done by double clicking the cell containing the
    name of the variable. When a row or column is hidden in this way the
    sum of the totals and sub-totals are unchanged.

5.  By [double clicking on a data cell]{.underline} within the table of
    a PivotTable you can see the underlying data that were used to
    derive the value in the cell. The underlying data will be displayed
    on an additional worksheet of the PivotTable workbook.

# **[Saving the Results]{.underline}**

The Excel files of the default reports are "read-only"; you can print
them or copy them but you can't change them permanently. However, if you
save a copy of the file under a new name, then any changes made to the
PivotTable can be preserved. Alternatively, if you wish to have full
spreadsheet capability in place of PivotTables capability, then copy the
table and paste it into a new spreadsheet as values, using Paste Special
in the Edit drop down menu. If the formats are also desired, use Paste
Special a second time to paste the formats as well. This version uses
approximately 50% less disk space when saved than saving the PivotTable.