Separating Table by Week

Hello,

I am working on a project where I am pulling in employee utilization totals from sheets being populated by data shuttle on a bi-weekly basis to a table that calculates totals.

The cloumns are basically using SUMIFs to populate the totals but I'm running into an issue where I can't separate these totals by week. I have the week number being calculate by month on the source sheets using this forumla.

=ROUNDUP((WEEKDAY(DATE(YEAR([Event Date]@row), MONTH([Event Date]@row), 1)) + DAY([Event Date]@row)) / 7)

This basically returns 1-5 depending on the week of month.

I need to be able to filter out by weeks 1-5 or some combination of them like weeks 1 and 2 or 3 and 4 etc.

I can't find an effective way to do this. you can see on the left where under week number I tried to play with the formula to read whatever number they put in that cell but I can't do multiple weeks this way and I don't think it's a scalable solution.

Here is a screenshot of one of the source sheets as well, they are all pretty similar.

Find more posts tagged with

Formulas

DataTable

Data Shuttle

Accepted answers

Bassam Khalil

Hi @Dakota Haeffner,

To solve your issue with separating totals by week based on your existing setup in Smartsheet, and taking into account your usage of Data Shuttle for periodic data updates, here are a few strategies you can consider:

1. Enhanced SUMIFS with Multiple Week Filtering

Since you want to aggregate data by specific weeks (e.g., weeks 1 and 2, or weeks 3 and 4), a straightforward SUMIFS approach with a single criterion won't suffice. You'll need a more complex formula to include multiple week numbers as criteria for summing. However, Smartsheet's formula capabilities are somewhat limited compared to Excel, and it doesn't support array formulas or directly filtering by multiple conditions in the same column in a SUMIF/SUMIFS function.

2. Helper Column for Week Groupings

One scalable solution is to use a "Helper Column" in your source sheets that groups weeks according to your requirements. For example:

Add a new column called "Week Group" or similar.
Use a formula to assign a group identifier based on the week number. This could be as simple as grouping weeks into pairs (e.g., "1-2", "3-4") or any other grouping logic that suits your analysis needs.
Your formula in this helper column might look like this, depending on your exact grouping logic:

=IF(OR([Week Number]@row = 1, [Week Number]@row = 2), "1-2",
 IF(OR([Week Number]@row = 3, [Week Number]@row = 4), "3-4", "5"))

This example assumes weeks are grouped as "1-2", "3-4", and "5" for simplicity.

3. Modified SUMIFS Using Helper Column

With the "Week Group" column in place, you can modify your SUMIFS formula to sum based on the group identifier instead of individual weeks:

=SUMIFS([Total Column Name], [Week Group Column Name], "[Group Identifier]")

Replace [Total Column Name], [Week Group Column Name], and [Group Identifier] with the actual names of your columns and the identifier of the week group you want to sum.

4. Dashboard Widgets or Reports for Dynamic Filtering

For a more dynamic and interactive solution, consider using Smartsheet Dashboards or Reports:

Dashboards: You can create Metric Widgets to display totals based on specific criteria, including your week groupings. While Dashboards offer great visualizations, they might not offer the granular control you're looking for in filtering by week groups directly.
Reports: Create a report to aggregate data across your sheets. You can include the "Week Group" column as a filter criterion in the report settings, allowing you to dynamically select which week groups to include in your totals. Reports can be very powerful for this type of analysis since they can pull in data from multiple sheets and allow for easy filtering and aggregation.

5. Automating with Data Shuttle

Ensure that your Data Shuttle configurations are set up to update the source sheets regularly, preserving the integrity of your "Week Number" and "Week Group" calculations with each data refresh.

These strategies combined should allow you to aggregate your employee utilization totals by week more effectively, providing the flexibility to filter and analyze the data as needed. If you encounter specific limitations or need further customization, consider integrating with external tools or services that can process the data outside of Smartsheet before importing the aggregated results back into your sheets.

All comments

Bassam Khalil

Hi @Dakota Haeffner,

1. Enhanced SUMIFS with Multiple Week Filtering

2. Helper Column for Week Groupings

One scalable solution is to use a "Helper Column" in your source sheets that groups weeks according to your requirements. For example:

Add a new column called "Week Group" or similar.
Use a formula to assign a group identifier based on the week number. This could be as simple as grouping weeks into pairs (e.g., "1-2", "3-4") or any other grouping logic that suits your analysis needs.
Your formula in this helper column might look like this, depending on your exact grouping logic:

=IF(OR([Week Number]@row = 1, [Week Number]@row = 2), "1-2",
 IF(OR([Week Number]@row = 3, [Week Number]@row = 4), "3-4", "5"))

This example assumes weeks are grouped as "1-2", "3-4", and "5" for simplicity.

3. Modified SUMIFS Using Helper Column

With the "Week Group" column in place, you can modify your SUMIFS formula to sum based on the group identifier instead of individual weeks:

=SUMIFS([Total Column Name], [Week Group Column Name], "[Group Identifier]")

Replace [Total Column Name], [Week Group Column Name], and [Group Identifier] with the actual names of your columns and the identifier of the week group you want to sum.

4. Dashboard Widgets or Reports for Dynamic Filtering

For a more dynamic and interactive solution, consider using Smartsheet Dashboards or Reports:

Dashboards: You can create Metric Widgets to display totals based on specific criteria, including your week groupings. While Dashboards offer great visualizations, they might not offer the granular control you're looking for in filtering by week groups directly.
Reports: Create a report to aggregate data across your sheets. You can include the "Week Group" column as a filter criterion in the report settings, allowing you to dynamically select which week groups to include in your totals. Reports can be very powerful for this type of analysis since they can pull in data from multiple sheets and allow for easy filtering and aggregation.

5. Automating with Data Shuttle

Ensure that your Data Shuttle configurations are set up to update the source sheets regularly, preserving the integrity of your "Week Number" and "Week Group" calculations with each data refresh.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.