Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    London, Gtr London, England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Calculated Fields (97)

    I am trying to create a pivot table in code that displays a data field for share of sales, this is simple enough to do when the share is against a common base, say sales as % of the national sales.

    With pt.PivotFields("Sum of Share")
    .Name = "% of National Sales"
    .Calculation = xlPercentOf
    .BaseField = "Region"
    .BaseItem = "National"
    .NumberFormat = "0.00%"
    End With

    The problem is that I am trying to display the regional share of the national sales and the local share of the regional share, as a single field.

    If anyone has any suggestions or ideas I would be most grateful for their help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Calculated Fields (97)

    It would be useful if you could attach an example of the data your pivot table is based on, and perhaps the (incomplete) pivot table. You can replace sensitive data by dummy data if necessary.

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    London, Gtr London, England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Calculated Fields (97)

    Hans

    Thank You for your interest, I apologize for the delay in responding to your posting, attached is a file of data and I include below a section of code to create the pivottable.

    Option Explicit

    Sub PivotBuild()

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "dump!R1C1:R1033C4", TableDestination:="", TableName:="PivotTable1"
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
    "ForecastNumber", "Data"), ColumnFields:="Area"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Revenue")
    .Orientation = xlDataField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Revenue")
    .Orientation = xlDataField
    .Name = "% Share"
    .Calculation = xlPercentOf
    .BaseField = "Area"
    .BaseItem = "United Kingdom"
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Data[All]", xlLabelOnly
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Data")
    .Orientation = xlColumnField
    .Position = 2
    End With

    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •