+ Reply to Thread
Results 1 to 4 of 4

Counting two data fields along one timeline

  1. #1
    Registered User
    Join Date
    05-11-2019
    Location
    Oslo, Norway
    MS-Off Ver
    2016
    Posts
    2

    Counting two data fields along one timeline

    XLSX file attached

    I have a data set of many tasks, all of which have an Open Time and some of which that have a Close Time.
    I want to configure a pivot diagram that counts all open times and all close times, placed next to each other and grouped by month.
    The picture shows the layout, but I haven't managed to configure it correctly.
    All help appreciated

    Skjermbilde.PNG
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by tcskauge; 06-03-2019 at 02:53 PM. Reason: Added xlsx as attachment

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,579

    Re: Counting two data fields along one timeline

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-11-2019
    Location
    Oslo, Norway
    MS-Off Ver
    2016
    Posts
    2

    Re: Counting two data fields along one timeline

    Attached a dummy xlsx file with data, and two separate diagrams that I would like to merge into one

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,684

    Re: Counting two data fields along one timeline

    Hello tcskauge and Welcome to Excel Forum.
    This proposal employs Power Query to transform the data by unpivoting the Open and Closed columns. (See columns A:C on sheet1)
    The pivot table is constructed such that the Open/Closed, is used for both legend series and values (count), and the dates, years and months, are used for the axis category.
    Let us know if you have any questions.
    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