+ Reply to Thread
Results 1 to 5 of 5

Pivot Table or chart to show data per month?

  1. #1
    Registered User
    Join Date
    06-09-2004
    Posts
    27

    Pivot Table or chart to show data per month?

    Howdy,

    I run a report from our ticketing system at work and would like to create some charts or pivot tables or whatever the best solution is to view the data. I uploaded a sample of what the data could look like when I run the report to pull the info.

    I can do simple Pivot Tables that just total up things but don't know how to do the part about breaking it out by month. The format of the dates I get is really stupid so if I'll need to break the date columes into two and split them on the '@' sign or something, I can do that too.

    Sample Data:
    https://www.dropbox.com/s/eki27m7ahr...Data.xlsx?dl=0

    I would like to have some various information such as:

    Count of Open & Closed tickets per month.
    --I thought a line graph with months across the bottom and numbers on the left. Could have two lines, one for open and one for closed, that would go across the chart
    --Or this could be a table with Open & Closed as the column headings and the list of months going down the side

    Count of tickets Closed per user
    --Like above, this could either be a line graph with a line for each user and the months across the bottom and number sup the side
    --Or it could be a table with Months down the side and users across the top

    If anyone can get me started in the right direction, that'd be great.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,717

    Re: Pivot Table or chart to show data per month?



    To attach an Excel file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post
    sandy
    [SIZE=1]How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    437

    Re: Pivot Table or chart to show data per month?

    You can do this with 2 helper col & create pivot to summarize the data. Please refer the attached excel for more insights.
    Attached Files Attached Files
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  4. #4
    Registered User
    Join Date
    06-09-2004
    Posts
    27

    Re: Pivot Table or chart to show data per month?

    Hi,

    Sorry for the late response but things got crazy at the end of the year.

    What I'm looking for is ONE table/chart that will show me tickets per user per month.
    I think I'm envisioning a line chart that would have months across the bottom, Number of tickets up the side, and each user in a different color.

    I'm attaching some actual sample data below. Columns B and J are the ones I would use.

    Based on the sample data, the left side should have the numbers 1, 2, 3. The bottom should have 01 and 02 (Jan/Feb). And there should be one line for John and one for Jane
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5,294

    Re: Pivot Table or chart to show data per month?

    This pretty much goes along with than_gold's suggestion, just applied specifically to the data provided in the file you attached and charted as to your specifications.
    The pivot table (sheet 2) utilizes month for row headers and values (count) and assigned to for column headers. The pivot chart then makes itself.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1