+ Reply to Thread
Results 1 to 4 of 4

How to put this required data in a correct pivot

  1. #1
    Registered User
    Join Date
    08-07-2019
    Location
    Amsterdam
    MS-Off Ver
    2019 PC
    Posts
    4

    Question How to put this required data in a correct pivot

    Hi all,

    I'm working on a small dashboard in which I can compare employee average picking times per product (per day) with eachother and see performance over a specific selected time T.

    I'm working with a data sheet in which i know the pick_start and pick_end times to determine the picking time of a certain order(=pick_end-pick_start).

    Next to this i have the employee names related to the order, and the number of products in this order. With this I determine the average time spent picking per product per order (=picking_time/product_count).

    My aim is to have a pivot chart with a slicer in which you can select the employee(s), a selectable period of time on the X-axis (weeks, months etc. as you wish) and the average picking time per order (total for the whole day) of the day on the Y-axis.

    I'm having difficulties with creating a working column for the table in which the average picking time per product of all orders of a certain employee are combined into a grandtotal-average for the overall day.

    The employees pick multiple orders per day, and thus have multiple averages of orders, which needs to be combined. However, I'm failing in succesfully creating a working formula (or another working option) to automatically generate the grand-average X for employee Y on date Z. This then has to be done for all employees and for all dates in my datasheet. (which is depending on the selected time rage, quite large)

    SQL output follows order of orders picked, and thus employees are randomly spread throughout the day.
    (OrderID, Date, Picker, Pick_start, Pick_end, Pick_time, Product_count, Picktime_per_product)

    If someone understands what I'm trying to say/do here: I would much appreciate your help and all additional instructions are welcomed.

    Kind regards,
    Matt
    Last edited by MMCRSP; 08-07-2019 at 11:11 AM.

  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,590

    Re: How to put this required data in a correct pivot

    Welcome to the forum

    Please 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
    08-07-2019
    Location
    Amsterdam
    MS-Off Ver
    2019 PC
    Posts
    4

    Re: How to put this required data in a correct pivot

    I created a mock file for a timespan of 5 days.

    The means in current pivot do not match means of the employee's picking time on a specific day if i calculate them with average(") on the data tab itself.

    To repeat: I'm failing in succesfully creating a solution to automatically generate the grand-average X for employee Y on date Z.

    This then has to be done for all employees and for all dates in my datasheet, to then include them in the pivot.

    If this still is not sufficient, please let me know. Thanks.

    Kind regards,
    Matt
    Attached Files Attached Files

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

    Re: How to put this required data in a correct pivot

    Perhaps the following will help.
    On Sheet1 the pivot table uses a calculated field (Av. Time) in the values section. The calculation is: ='Time picked' /'Product count'
    The time formatting has also been changed to h:mm:ss;@
    Note that I only did limited testing as can be seen on the Data sheet cell J35, so I suggest you do more testing.
    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