Pivot table help

[austenr]

In the attached workbook I'm trying to get the pivot table on sheet2 to show actual calls for each person. All I get is a 1 for each person. What I am after is a way to show calls received for each person on a daily basis and then also a grand total received per week and per month.

[Paul_Hossler]

This is using Excel 2016

1. I changed the data source to end at row 60, the end of real data

2. Use Sum of Calls field settings to select Sum

20245


3. Right click in a date cell and select Group, Days and Months
20246


4. Use Months field setting to select Sum

Don't know any easy way to group by week, but if you just select Days in #3, you can then select 7 days

20247

[austenr]

thanks paul

[austenr]

one other thing. i eventually want to make a dashboard with a slicer for date selection. I also will be adding to the table for the pivot table daily. is there a way to make your dashboard items default to the last row unless you select another date?

[Bob Phillips]

You could do it with some VBA.


Dim maxdate As Long
Dim pvtcount As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet.PivotTables("PivotTable6").PivotFields("Date")
maxdate = .PivotItems.Count
If .PivotItems(maxdate) = "(blank)" Then maxdate = maxdate - 1

For i = 1 To .PivotItems.Count

.PivotItems(i).Visible = i = maxdate
Next i
End With

Application.ScreenUpdating = True

There may be a better way, but I couldn't see it, and you would need some event to trigger this code.

[Paul_Hossler]

This works in Excel 2016

I extracted / modified a bit of code that I use in a PT with a Page Field named "Close Date" to select the last date

You can add this macro to the ThisWorkbook and maybe change to suit your situation




Option Explicit
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
If Sh.Name <> "MySheet" Then Exit Sub
If Target.Name <> "MyPivot" Then Exit Sub

With Target
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
.RefreshTable
.TableRange1.EntireColumn.AutoFit

On Error Resume Next
.PivotFields("Close Date").CurrentPage = .PivotItems(.PivotItems.Count).Caption
On Error GoTo 0

End With
End Sub

[austenr]

Hi Paul and Bob. Thanks for the help. Cant seem to get it to work with Pauls code. Ill post what I have. What I really need is to show the current day in the dashboard with an option to select a date other than today or a range of dates.

[Bob Phillips]

Cant seem to get it to work with Pauls code. Ill post what I have. Nor can I.

Do you have Power Pivot?

[Paul_Hossler]

My sample used the Page Field, so that's why it didn't work for you


This is in the attachment


1. In the Sheet1 code module -- it refreshes the PT when you add more data or change existing data, finds latest date, and updates slicer




Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxDate As Date
Dim maxCaption As String
Dim oPI As PivotItem

If Intersect(Target, Cells(1, 1).CurrentRegion.EntireColumn) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Worksheets("Daily Report").PivotTables(1)
.PivotCache.Refresh
.PivotFields("Date").ClearAllFilters
.PivotCache.MissingItemsLimit = xlMissingItemsNone
maxDate = 0
For Each oPI In .PivotFields("Date").PivotItems
If maxDate < CDate(oPI.Caption) Then
maxCaption = oPI.Caption
End If
Next

For Each oPI In .PivotFields("Date").PivotItems
oPI.Visible = (maxCaption = oPI.Caption)
Next
End With
Application.EnableEvents = True
End Sub




I was changing Maggie's last date and it seems to work OK

[austenr]

My sample used the Page Field, so that's why it didn't work for you


This is in the attachment


1. In the Sheet1 code module -- it refreshes the PT when you add more data or change existing data, finds latest date, and updates slicer




Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxDate As Date
Dim maxCaption As String
Dim oPI As PivotItem

If Intersect(Target, Cells(1, 1).CurrentRegion.EntireColumn) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Worksheets("Daily Report").PivotTables(1)
.PivotCache.Refresh
.PivotFields("Date").ClearAllFilters
.PivotCache.MissingItemsLimit = xlMissingItemsNone
maxDate = 0
For Each oPI In .PivotFields("Date").PivotItems
If maxDate < CDate(oPI.Caption) Then
maxCaption = oPI.Caption
End If
Next

For Each oPI In .PivotFields("Date").PivotItems
oPI.Visible = (maxCaption = oPI.Caption)
Next
End With
Application.EnableEvents = True
End Sub




I was changing Maggie's last date and it seems to work OK

[austenr]

Paul,
.Could you post a working solution with you changes? Thanks

[Paul_Hossler]

Thought I did in post #9, but here it is again

Code is in the Sheet1 code module

20255


I was changing Maggie's date to test

[austenr]

thanks paul