Slicing and dicing data using hierarchy nodes

Spotfire provides a set of OVER functions to allow you to aggregate data over the nodes of a hierarchy, usually a date hierarchy but not necessarily. At its simplest level, the OVER function is a means to define at what granularity you wish to aggregate a value. For example, you could create the following calculated column in the baseball data:

Avg([Hits]) OVER [Team]

The result would be the average number of hits achieved by the players in each team. As each team has more than one row in the data, the lowest granularity of which is a player, you will end up with the following result:

The OVER function has some more advanced and challenging methods. On their own, these methods may not initially seem particularly useful to you and may even be a little hard to understand. However, they become very powerful when used in expressions.

In Chapter 1, Show Me the Data, when considering the key data concept of rows and columns, we saw that you cannot subtract...