PIVOT TABLE HELP!

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
Sub GroupPivotDates(InPivTable As Excel.PivotTable, strMonthOrQuarter As String)

Dim xlPivDateField As Excel.PivotField, xlGroupRange As Excel.Range

Set xlPivDateField = InPivTable.PivotFields("Dates")
Set xlGroupRange = xlPivDateField.DataRange
If strMonthOrQuarter = "Month" Then
xlGroupRange.Cells(1).Group Periods:= _
Array(False, False, False, False, True, False, True)
Else
xlGroupRange.Cells(1).Group Periods:= _
Array(False, False, False, False, False, True, True)
End If


Set xlPivDateField = Nothing
Set xlGroupRange = Nothing
End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
Sorry the whole message didn't copy

I am creating a pivot table from a query in access. I am able to bring in all the data OK and group it by date. The problem is I want the average for each month once it is grouped by month and I am getting the sum of each month at the current moment. My call to the sub procedures is below:

Code:
'Produce Pivot table for avg daily information for shares and executions
Call ProducePivTable(PivTable, avgdailysheet, prange1, "SharesExecuted")
With PivTable
    'Add Fields
    .PivotFields("tradedate").Orientation = xlRowField
    .PivotFields("tradedate").Caption = "Dates"
End With
Call GroupPivotDates(PivTable, "Month")

With PivTable
    .PivotFields("SumOfsharesexecuted").Orientation = xlDataField
    .PivotFields("SumOfsharesexecuted").Caption = "AvgSharesExecuted"
End With

PivTable.PivotFields("SumOfSharesexecuted").Function = xlAverage

The code for the produce pivot table is
Code:
Sub ProducePivTable(xlPivTable As Excel.PivotTable, xlDataSheet As Excel.Worksheet, _
                            xlTableDest As Excel.Range, tblename As String)


    Dim xlapp As Excel.Application, xlLastRow, xlLastRow2 As Excel.Range, xlPivotRng, xlPivotRng2 As Excel.Range, xlLastCell, xlLastCell2 As Excel.Range
    Dim LastCol As Integer, LastRow, LastRow2 As Long
       
    'Creates pivot tables
    LastRow = xlDataSheet.Range("A" & xlDataSheet.Rows.Count).End(xlUp).Row
    xlDataSheet.Range("A2:A" & LastRow).NumberFormat = "mm/dd/yyyy"
    
    Set xlLastCell = xlDataSheet.Cells(LastRow, 2)
    Set xlPivotRng = xlDataSheet.Range(xlDataSheet.Range("A1"), xlLastCell)
    Set xlPivTable = xlDataSheet.Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        xlPivotRng).CreatePivotTable(TableDestination:=xlTableDest, _
        tablename:=tblename, DefaultVersion:=xlPivotTableVersion10)
      
    Set xlPivotRng = Nothing
    Set xlLastRow = Nothing
    Set xlapp = Nothing
End Sub

and the code for group by date is
Code:
Sub GroupPivotDates(InPivTable As Excel.PivotTable, strMonthOrQuarter As String)

    Dim xlPivDateField As Excel.PivotField, xlGroupRange As Excel.Range
    
    Set xlPivDateField = InPivTable.PivotFields("Dates")
    Set xlGroupRange = xlPivDateField.DataRange
    If strMonthOrQuarter = "Month" Then
        xlGroupRange.Cells(1).Group Periods:= _
            Array(False, False, False, False, True, False, True)
    Else
        xlGroupRange.Cells(1).Group Periods:= _
            Array(False, False, False, False, False, True, True)
    End If

           
    Set xlPivDateField = Nothing
    Set xlGroupRange = Nothing
End Sub

I have tried to insert the average function in multiple locations and have still had no luck. I would appreciate ANY help!!!! Thanks
 

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
I have resolved the issue. You need to adddatafields not pivot fields.

With PivTable
.AddDataField .PivotFields("SumOfsharesexecuted"), "AvgSharesExecuted", xlAverage
end with
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top