Pulling data from multiple shifts?

Engineo

I am attempting to build a dashboard for tracking production performance.

I have a form that is completed by each shift. I believe I need to pull that info up into a calculation sheet and from their I can populate the dashboard.

I am getting hung up on have multiple shifts on the same date.

I want to be able to see performance of each shift for the last day, week, & 30 days on the dashboard.

If I use a max, min type function on the date column I only get the oldest or most recent entry and cannot figure out how to pull the individual shifts up into my calc sheet.

Here is a simple sample of my data

Kelly Moore

Hey @Engineo

Here is one approach that may get you what you need. Yes, this approach uses another sheet to collect the information from your source sheet.

I changed your example information slightly to have data for each time period you described.

Below is how I set up my metric sheet

I will show the 1st Shift calculations for each of the Periods. To change for the other shifts you will replace "1st" with either "2nd" or "3rd" in its appropriate column. Only references to "2nd" will be in your 2nd Shift column. Only references to "3rd" in 3rd shift column.

All of these calculations will be in the appropriate row of the 1st Shift column

Yesterday

=SUM(COLLECT({Source sheet units per shift actual}, {Source Sheet Shift}, "1st", {Source sheet Date}, TODAY(-1)))

This Week

=SUM(COLLECT({Source sheet units per shift actual}, {Source Sheet Shift}, "1st", {Source sheet Date}, WEEKNUMBER(TODAY()) = WEEKNUMBER(@cell)))

Last Week

=SUM(COLLECT({Source sheet units per shift actual}, {Source Sheet Shift}, "1st", {Source sheet Date}, WEEKNUMBER(TODAY()) - 1 = WEEKNUMBER(@cell)))

This Month

=SUM(COLLECT({Source sheet units per shift actual}, {Source Sheet Shift}, "1st", {Source sheet Date}, MONTH(TODAY()) = MONTH(@cell)))

Last Month

=SUM(COLLECT({Source sheet units per shift actual}, {Source Sheet Shift}, "1st", {Source sheet Date}, MONTH(TODAY()) - 1 = MONTH(@cell)))

See if this time periods work for you. If they are not what you need, let me know and we can tweak or build more.

Will this work for you?

Kelly

Engineo

First apologies for the late reply. It has been a busy few days.

Thank you very much. I think this will work. I am hoping to carve out some time to give it a try this week!

Engineo

@Kelly Moore - your solution works great. However, I cannot seem to get this to work with text values. I tried a few variations like VLOOKUP unsuccessfully. Do you have any suggestions on how to pull the Part# data from the associated row?

For instance you suggested this for numbers but I need the Part# from the associated row?

Yesterday

=SUM(COLLECT({Source sheet units per shift actual}, {Source Sheet Shift}, "1st", {Source sheet Date}, TODAY(-1)))

Kelly Moore

Hey @Engineo

The SUM function can only sum numbers. Can you give examples of the text you refer to- and if the format of the entries vary, please provide enough examples to see the variation. There might be work-arounds we can use. I immediately wondered if it was text, do you need sums or do you need counts?

Kelly

Kelly Moore

Hey

Sorry I answered the above from my phone and didn't see the part# column in the cell window. When you say you the part number data, what data about the part numbers do you need?

Engineo

@Kelly Moore

In the data collection form, the user chooses the Part # from a drop down. So in this example the first row could be any one of up to about 10 Part#'s. So I want to populate the Part# associated with the row identified by the functions you provided above.

Kelly Moore

Hey @Engineo

Because you're asking to categorize shifts, time periods and part numbers simultaneously, we have to summarize your data differently. I'll list two approaches.

Basic reports, one for each of the time periods, using grouping and summaries, could give you what you need for your dashboard. The second approach uses Pivot App, which might be an option if your license gives you access.

I added helper columns (checkboxes) that indicate what time frame I was collecting so that the report had something to filter against. For example, I have a column called Last Week. A checkbox is checked for all instances where the date is 'last week'. Another checkbox column is 'Last Month', etc.

Here's what the outputs from the two different approaches would look like. Do either of these approaches look like they will work for you? If yes, I can help you build it.

Smartsheet regular report- you can swap whether Part numbers or Shifts are the top level filter. Shown is Shift as top level. Since this is a report, it could be pulled directly into a dashboard

Here's the output of Pivot App. Your license must give you access to Pivot App to use this approach.

-Kelly