Excel 2007 Pivot Table Calculated Field

[slink9]

Hello to all,

I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.

I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).

1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance

This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)? Suggestions?

 

[Andrew Poulsom]

You can add the value column as a data field again, then Show data as % Difference From Base field PINSAL Base item (previous).

 

[slink9]

What is the VBA code for that? Here is what I have so far (which is unsuccessful)

 Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets(nmhld).Range("A1:" & rtcol & botrow))
    Set pvt = pvc.CreatePivotTable(TableDestination:=wks.Range("B4"), TableName:="SlsPIRES")
    'pvt.ManualUpdate = True
    With pvt
        .PivotFields("Period").Orientation = xlColumnField
        .PivotFields("Period").Position = 1
        .PivotFields("YEAR").Orientation = xlColumnField
        .PivotFields("YEAR").Position = 2
        .PivotFields("SEGMENT").Orientation = xlRowField
        .PivotFields("SEGMENT").Position = 1
        '.PivotFields("PISTE").Orientation = xlRowField
        '.PivotFields("PISTE").Position = 2
        .PivotFields("PINSAL").Orientation = xlDataField
        '.PivotFields("PINSAL").Position = 1
        .RowGrand = False
        .TableStyle2 = ""
        .RowAxisLayout xlTabularRow
        .CalculatedFields.Add "DollarVariance", "=[12 - 2008]-[12 - 2007]"
        .PivotFields("DollarVariance").Orientation = xlDataField
        '.PivotFields("DollarVariance").Position = 2
    End With

 

[slink9]

Sorry, submitted a little too soon. The names in brackets are assigned column names by the pivot procedure. I will need to show both the dollar difference along with percent difference.

While we are at it, is there a way to hide the subtotal? RowGrand is hidden successfully but I want to hide the subtotal row also since the total of 2007 and 2008 values is useless.

 

[Andrew Poulsom]

ADVERTISEMENT

I would record a macro while doing it manually to get the code.

 

[slink9]

Well, that was highly unsuccessful. If I drag PINSAL into the Values section I see three extra TOTAL columns and they have the same numbers in them. This makes sense so far. Then I change the Sum of PINSAL2 value field settings to Difference From (with PINSAL and (previous) selected) and those values change to #N/A. What am I missing???

 

[Andrew Poulsom]

ADVERTISEMENT

Sorry, I don't know what you are missing. It worked when I tried it in Excel 2000.

 

[slink9]

No idea what was missing either other than the fact that this is 2007 and it works quite differently. I did get to the result that I wanted, although it is a workaround. I copied the main columns that I wanted, did a pastespecial as values in another area of the sheet, and then added the calculation columns to the right. That worked out perfectly and it eliminated the subtotals column that I could not find a way to remove.

 

[Myproblem]

I have the similar issue with VBA PivotTable Calculated Field.
It works fine, but It would be great and I want to greate and use Name Range that would be used as part od Calculated Field.

My Problem is that even if I create Name Range in other sheet (because PivotTable with wanted Calculated Field is greated via VBA) and it should be in other sheet and PivotTable is.

The formula is basicly:
....Formula:="=Volume_T/Volume_T_Before/Date_MTD*365"

Date_MTD is problem, because it is Name Range in other sheet the pivot is and value is 90.
Error is: Run-time error 1004
Unable to get the PivotFields property of the Pivot Table class

Is it possible to get this to work, or is it possible to use name range in pivot table calculated field in this fashion?

thx

 

[Andrew Poulsom]

No, you can only use the names of the pivot table fields.