Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables (2003)

    Hi,

    Please see attached. Trying to make Pivot Charts for Help Desk. I can do these manually but that's difficult to manage long-term. Hence trying to use Pivot method to automate the process.

    I want to geneate the following graphs:

    Graph 1: Just the Total No of Tickets (Col G) per date (Col A) - per Dept. (Col [img]/forums/images/smilies/cool.gif[/img]. - Tells me how many tickets / day & from whom.

    Graph 2: Ditto, but breakdown for Impact (Cols C to F) instead of Dept.

    Graph 3: Ditto, but for Priority (Cols H to K).

    Please advise me which pivot entries to make as the ones I choose end up as a dog's breakfast.

    TIA,

    BobD.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Tables (2003)

    It would help if you created the 3 charts manually for us to see what you are after....

    You might try just recording a macro while doing it manually, it should get you 80+% of the way there.

    If you are only doing these 3 charts and only the data changes, why not just do them one time manually based on sheet with a dynamic named range, Theny you can just add new data or copy over the old data and the charts are created...

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (2003)

    Thanks, actually I'll need many more graphs, these 3 were just to get the ball rolling. Also the no. of rows will increase, the current data-set is small, the actual data-set may have 50 entries per day. Plus the users of the actual sheet want pure automation from IT tools (sound familiar?!)

    Ok, here's Graph 1 shown graphically (I couldn't get ChartWizard to generate the same):

    Basically, a daily Breakdown of the Ticket Type.

  4. #4
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (2003)

    Hi,

    I'm thinking if we can do this one, maybe I can reuse the same technique for the others.

    TIA.

    Bob.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Tables (2003)

    I think this may be what you are after, though I do not see how the dataset you have would give example chart you list...

    <pre>Sub MakePivotChart1()
    Dim PT As PivotTable
    Dim wPivot As Worksheet
    Dim wData As Worksheet

    Set wData = ActiveSheet
    Set wPivot = Worksheets.Add
    Set PT = wPivot.PivotTableWizard( _
    SourceType:=xlDatabase, _
    SourceData:=wData.Range("a1").CurrentRegion, _
    TableDestination:=wPivot.Range("A1"))
    With PT
    .AddFields RowFields:= _
    "Ticket Open Date", _
    ColumnFields:="Source Dept"
    .PivotFields("Total No of Tickets"). _
    Orientation = xlDataField
    End With
    Charts.Add
    End Sub</pre>


    I still think it may be easier to create the charts of interest and change the dataset. Using the pivot chart is easy enough to drag the desired fields on the one chart.

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Tables (2003)

    Another thought:
    To make charting easier (especially with a pivot table/chart and builtin features) would be to store your data as real database not in the fashion you have it.

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center valign=bottom>1</td><td align=center valign=bottom>Ticket Open Date</td><td align=center valign=bottom>Source Dept</td><td align=center valign=bottom>Impact</td><td align=center valign=bottom>Priority</td><td align=center valign=bottom>Ticket Status</td><td align=center valign=bottom>Ticket Final Status</td><td align=center valign=bottom>2</td><td align=center valign=bottom>6/6/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Critical</td><td align=center valign=bottom>Emergency</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>3</td><td align=center valign=bottom>6/7/2006</td><td align=center valign=bottom>HR</td><td align=center valign=bottom>Medium</td><td align=center valign=bottom>High</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>4</td><td align=center valign=bottom>6/19/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Critical</td><td align=center valign=bottom>Emergency</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>5</td><td align=center valign=bottom>6/20/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Critical</td><td align=center valign=bottom>Emergency</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>6</td><td align=center valign=bottom>6/22/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Low</td><td align=center valign=bottom>Low</td><td align=center valign=bottom>Resubmission</td><td align=center valign=bottom>Escalated</td><td align=center valign=bottom>7</td><td align=center valign=bottom>6/26/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Low</td><td align=center valign=bottom>High</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>8</td><td align=center valign=bottom>6/29/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Low</td><td align=center valign=bottom>High</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>9</td><td align=center valign=bottom>7/5/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>High</td><td align=center valign=bottom>Low</td><td align=center valign=bottom>???</td><td align=center valign=bottom>Failed</td><td align=center valign=bottom>10</td><td align=center valign=bottom>7/12/2006</td><td align=center valign=bottom>Sales</td><td align=center valign=bottom>Critical</td><td align=center valign=bottom>Emergency</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td><td align=center valign=bottom>11</td><td align=center valign=bottom>16/07/2006</td><td align=center valign=bottom>Operations</td><td align=center valign=bottom>Critical</td><td align=center valign=bottom>High</td><td align=center valign=bottom>Rejected</td><td align=center valign=bottom>Failed</td><td align=center valign=bottom>12</td><td align=center valign=bottom>18/07/2006</td><td align=center valign=bottom>Admin</td><td align=center valign=bottom>Low</td><td align=center valign=bottom>Medium</td><td align=center valign=bottom>Approved</td><td align=center valign=bottom>Closed</td></table>

    This setup is more complact andeasier to see all at once on the screen
    you can even use validation to have them select the items from a list
    Can be summarized more easily in Pivot table/chart

    Steve

  7. #7
    Lounger
    Join Date
    Jun 2006
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (2003)

    That's a good idea. I've put that into the attached Excel file.

    Here's a challenge: I need to group the results into weekly batches.

    The pivot chart's option allows me to select a particular day, but I need to specify a week, i.e. a start day and end day.

    So, I want to generate separate tables and charts for each week.

    Hence within the same excel file, but different sheets need to have:

    - July Week 1 - Table + Chart
    - July Week 2 - Table + Chart
    - July Week 3 - Table + Chart

    Can this be done with pivot tables/charts?

    TIA.

    Bob.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Tables (2003)

    If you want weeks to start on Monday you could add a column (named eg Week Start):

    =B3-WEEKDAY(B3)+2

    Then you could use this column in the pivot table...

    Steve
    PS <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>: the first column and the first row are duplicates of the row/column headings and are probably not needed.

Posting Permissions

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