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
Bookmarks