Weekly Sum for Rolling 1 year (52 weeks)

Options

I have a smartsheet grid that tracks the number of units sold each day - every day is a different row.

Every Monday, it sums the number of units sold for the past week. For example, on Monday 10/18 it will sum the total number of units sold between Monday 10/11 - Sunday 10/17. This is displayed in a weekly total column (only every seventh row, on Mondays)

Currently the sheet has data from the beginning of 2020 through today. It is displayed in a dashboard using a widget to pull in the weekly total and the data label - the chart is very crowded with over a year and a half of data.

My manager would like to see the weekly total with rolling 1 year of data (52 weeks). I'm at a loss for how to set this up - please help!

Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @katherineliu

    It sounds like you've done most of the hard work already!

    Since you have a column that identifies the date, you could set up a Report which filters by this date column to only show the past 52 weeks or 365 days, as well as only pulling in rows that have the totals in the Weekly Total Column.


    You could then even use the Summary function to SUM together all of your weekly totals for an overall total. Here's more information on creating a Report, and Webinar on Reports, too.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @katherineliu

    It sounds like you've done most of the hard work already!

    Since you have a column that identifies the date, you could set up a Report which filters by this date column to only show the past 52 weeks or 365 days, as well as only pulling in rows that have the totals in the Weekly Total Column.


    You could then even use the Summary function to SUM together all of your weekly totals for an overall total. Here's more information on creating a Report, and Webinar on Reports, too.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!