How to change labels on chart generated from a pivot table

[bstreiss]

Hello,
I'm really new to pivot table, and already a bit frustrated. Here's a screen grab of some sample data I'm working with.

The pivot table dates are created by a basic Group By (Day=7), and appear to be a texted based cell. I don't care much about the display of the pivot table, but I need to chart to show only the first day of the week in the format of the raw data.

Any pointers? BTW, I'm using 2010 if it matters.

Thanks tons!

 

[Domenic]

I think we can use the PivotTableUpdate event handler to automatically change the captions for the grouped pivot items whenever the pivot table is updated and, in turn, the pivot chart will automatically change accordingly. Place the following event handler in the code module for the sheet containing the pivot table (right-click the sheet tab, select 'View Code', and copy/paste the code)...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_PivotTableUpdate([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] PivotTable)

    [COLOR=darkblue]Dim[/COLOR] pt [COLOR=darkblue]As[/COLOR] PivotTable
    [COLOR=darkblue]Dim[/COLOR] pf [COLOR=darkblue]As[/COLOR] PivotField
    [COLOR=darkblue]Dim[/COLOR] pi [COLOR=darkblue]As[/COLOR] PivotItem
    [COLOR=darkblue]Dim[/COLOR] Pos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] pt = Target
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] pf = pt.PivotFields("Date")
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    [COLOR=darkblue]If[/COLOR] pf [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] pf.Orientation = xlHidden [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Application
        .EnableEvents = [COLOR=darkblue]False[/COLOR]
        .ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    pt.ManualUpdate = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] pi [COLOR=darkblue]In[/COLOR] pf.PivotItems
        Pos = InStr(pi.Caption, "-")
        [COLOR=darkblue]If[/COLOR] Pos > 0 [COLOR=darkblue]Then[/COLOR]
            pi.Caption = Trim(Left(pi.Caption, Pos - 1))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] pi
    
    pt.ManualUpdate = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Application
        .ScreenUpdating = [COLOR=darkblue]True[/COLOR]
        .EnableEvents = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Now, refresh (Alt+F5) the pivot table, and the pivot chart should change accordingly.

Hope this helps!

 

[bstreiss]

Thanks Domenic!
Way more complicated than my newbie brain ever would have come up with.

I know it's probably not the "correct" way to solve it, but I came up with the idea of adding a new column to the pivot table which used the min date. I then used a regular chart to build what I wanted and referenced individual columns rather than the actual pivot table.

 

[Domenic]

Actually, that's a great alternative. One that I probably should have offered in the first place. Thanks for your feedback.