Pivot Table Calculated Column?

My excuse is that I’ve got a heavy cold, and my brain is definitely not firing on all cylinders. I’ve found a problem which ought to have a simple Excel (not VBA) solution, but I can’t work out what it is. Dick, Jon, anybody: please put me out of my misery.

I’m currently revising some documentation for a leading financial OLAP framework (ActivePivot), so I’m much involved with pivot tables. I was toying with one of my own, which summarized Sales data (in Excel, not OLAP) – the usual stuff:

(Yes, it’s old data, and I need to change the dates). Simple enough to have Min and Max of Sales:

But suppose that I want to see the difference between Max and Min. Surely there is somewhere to enter the formula ‘=Max – Min’ (or possibly something like ‘=Values.Max – Values.Min’). But where?

It’s not a Calculated Field, since that is essentially a calculated column on the data table. I could, for example, have a field ‘= Sales / Contacts’.

It’s not a Calculated Item, since that aggregates values for dimension members. For example, I could aggregate Jan+Feb+Mar into a Q1 value. I could even do differences, for example, Jul-Dec minus Jan-Jun. However, although the Min and Max Values (fields) appear as column headers, they are not a dimension.

I could have formulas that sit outside the pivot table:

  • using GETPIVOTDATA on individual cells
  • using an array formula to subtract the whole Min array from the whole Max array.

However, these do not, of course, adapt to changes in the pivot table layout, such as filtering member values.

Since an aggregation function could be any function that takes an array of values and returns a single value, we could have more sophisticated derived measures based on the built-in aggregation functions (Max, Average, Stdev, etc).

The OLAP framework mentioned above is of course a much more sophisticated beast. There are ways to calculate additional fields before aggregation in the hypercube (cf. pivot cache), custom aggregation functions within the hypercube, and post-processor functions to calculate additional values. I think the last of these would be the appropriate one here.

Is there a VBA solution? Right now, I haven’t penetrated deep enough into the Byzantine complexities¬† of the pivot table object model. A quick look indicated that PivotField.function is an enumeration of the built-in types. So there’s no obvious hook for a custom aggregation function.

Now I’m going away to cough a lot…

About these ads

5 Responses to “Pivot Table Calculated Column?”


  1. 1 Dick Kusleika September 30, 2010 at 6:34 pm

    The next time I see a meaningful use of calculated fields or items will be the first time. Beyond the most rudimentary stuff, they’re useless.

    I would add another column to the source data with this array formula

    =MAX(($A$2:$A$51=A2)*($E$2:$E$51))-MIN(IF($A$2:$A$51=A2,($E$2:$E$51),””))

    And take the max of that in a new pivot item.

  2. 2 Roy MacLean October 1, 2010 at 3:22 pm

    Thanks, Dick.

    I’ll take that as confirmation that I’m not missing something in the pivot table facilities. As you say, calculated fields in particular are a bit limited.

    Interesting that this was such a simple requirement to state – but presumably hard for the pivot table/cache implementation to do post-aggregation.

    The array-formula (ra! ra!) solution works, as you say, and looks a bit better with Table addressing. The assymmetry between the MAX and MIN cases (because of handling the false-case zeroes in the MIN case) is a bit subtle.

    Since this is a pre-aggregation solution, we would potentially need a calculated MaxMinDiff column for each of the three dimension columns. In this case it’s Month, but could also be for Region or Sales Rep.

  3. 3 Dick Kusleika October 1, 2010 at 4:40 pm

    “for each of the three dimension columns. ”

    Ooh, that complicates it then. That’s usually when I give up and move the formulas outside of the PT or roll my own PT with array formula. I wish there was a better solution.

  4. 4 Andy Pope October 5, 2010 at 8:48 am

    This code will add formula to the right of the columns.

    Although you would also need further code to remove previous formula when the row fields change and the resulting number of rows in less than previous.

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    With Target.DataBodyRange
    With .Columns(.Columns.Count)
    .Offset(-1, 1).Resize(1, 1) = “Diff”
    .Offset(, 1).FormulaR1C1 = “=RC[-1]-RC[-2]”
    End With
    End With

    End Sub


  1. 1 Pivot Table Calculated Column – 2 « Roy MacLean's VBA Blog Trackback on October 5, 2010 at 8:56 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




September 2010
M T W T F S S
« Aug   Oct »
 12345
6789101112
13141516171819
20212223242526
27282930  

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: