Wednesday, July 25, 2007

simple, hands-on example: pivot tables explained

This tutorial is short, but must be read word-for-word




This short tutorial aims to give you a simple hands-on example of what pivot tables are

Pivot tables allow you to report and analyze data.

It provides automatic filtering and grouping of data depending on the selections you make.


* Let us illustrate it with a gradual approach:


Our Data sample (note that the values for each COLUMN represent a single result, hence related):


VA       a          b          c          d

VB       e          e          f           g

VC       h          i           j           k

VD       l           m         m         n

VE       o          o          o          p


1. Single variable


1.1 For a single list of data, if there is no repetition of the data -> you can only get the same table


Pivot Table 1.1:

VA       a          b          c          d

TT        1          1          1          1


1.2 For a single list of data, with repetition -> you can get a summary (count) of all instances in the list


Pivot Table 1.2:

VC       e          f           g

TT        2          1          1<-- grouping count


2. Two variables


2.1 If the data is unique for both sets -> you can get the list of the data or a matrix with a single entry in each cell


VA/VC            h          i           j           k

a                      1          0          0          0

b                      0          1          0          0

c                      0          0          1          0

d                      0          0          0          1


2.2 If one of the sets has repetitions -> you can get (besides using one variable only)

            2.2.1 a list grouping one list against the other (composite row / column list)

            count VA

            e          a          1

                        b          1


            f           c          1


            g          d          1




            count VC

            o          l           1

                        m         2


            p          n          1


            2.2.2 a matrix showing the breakdown of the two variables in the same row (ie more than one entry in each cell)


            VB / VE           o          p

            e                      2          0

            f                       1          0

            g                      0          1


            2.2.3 a count of the variable for all instances of the other variable (group by the second)

            count VA


            e          2

            f           1

            g          1


3. Three or more


Expand the concept of grouping and filtering


* "Page" is a location for specifying overall filters on the variables

* You can also filter on the rows and columns selection

* Obviously, this methodology can get complex with large dimensions of data (refer to "Cubes")

No comments:

Post a Comment