Weekly Report

RolandoN

Hello Smartsheet folks,

Does anyone know how I can create a report that only show data results every week? I have a data sheet/report that shows what store was complete, their unique information and the date their installation was complete, for the entire month.

From this data sheet/report, I want a weekly report that only shows, for example, completions from 5/13 through 5/19, then the following week's report will only show completions from 5/20 through 5/26 and so on.

The only way I can see doing this is manually. Meaning going into my data sheet/report and selecting a date range to show, then sending that data filter as an attachment.

It'd be great if I could automate this weekly report. As it currently stands, if I were to create an automated weekly report email, it would include everything, all completions, all dates, in the email.

Any suggestions/workarounds would be appreciated.

Thanks,

Rolando

Will Jeffords

Hi @RolandoN, this is a great use case for Sheet Summary I think!

If you could create a set of Fields in Sheet Summary on your data sheet that capture the key pieces of info that you want on your report for "This Week" (use formulas that perhaps incorporate matching WEEKNUMBER and TODAY() as criteria for the data fields in the summary, then you can create a Sheet Summary Report that will auto update to only include the info for the current week.

I thought of this as I use something similar in my PTO Tools that I created in Smartsheet where I have metrics on a dashboard that show: "Who's out this week", "Who's out next week", and "Who's out today" that leverages Sheet Summary fields this way. My example is a dashboard instead of a report, but the fact that we can create reports on Sheet Summaries instead of Row Reports makes your case feasible I believe.

In my suggestion, your "this week" report (or "last week" if you are wanting it to be on a complete week…maybe do both?) will be updating week-to-week, so you would need to take the simple manual step to export or otherwise capture the report if you are trying to retain historical views, but you could also make your Sheet Summary fields formulas such that you could simply map/adjust the specific weeknumber that you use for the week you wish to see!

If useful for us to jump on a zoom to investigate further - let me know, I think you have a cool use case here!

Best of luck!

Will

RolandoN

Thank you Will for your feedback. I like where you're going with this. Ultimately, my sheet/report would be embedded in my dashboard. Yes I would like to do/show both 'Last week completion' and 'This week completion'.

I do need help as I've never used WEEKNUMBER at all. I am available and can create a Zoom call anytime before 11AM PST or after 1PM PST today if you can chat.

Thanks,

Rolando

RolandoN

Found it! I used this formula against a date column - =IF(TODAY() > Date95, "Y", "N"), then I will this column where Y and N are to generate my report only showing rows that have Y. But I also leveraged WEEKNUMBER in another sheet. Thank you for the inspiration.

RolandoN

Not completely resolved. While this formula identifies dates that less than today, it identifies everything in the past. Does anyone know how I can limit the formula to only look back a week?

I use this to mark if the date is less than today - =IF(TODAY() > Date95, "Y", "N"), which works. The formula gives me a "Y" response from everything in the past.

Is there a formula I can add to this to give me a "N" response if the date in column Date95 is no more than a week in the past and a "Y" if within the week from TODAY?