OPTION FOR CURRENT YEAR FILTER

Sean Flannagan · 07/06/24

I have yearly reports that I want the current year to populate, currently I have 2024 in place so it is working nicely. However I would like to not to go in and update to the current year every year. Is there something I could input instead of "2024" to get the current year? How about if I want last years data displayed?

Screenshot 2024-07-05 at 10.08.37 PM.png

jmyzk_cloudsmart_jp · 07/09/24

jmyzk_cloudsmart_jp · 07/06/24

Hi @Sean Flannagan

You can add helper columns, such as "This Year" and "Last Year," as shown in the demo sheet below, and use them as filters for your report.

https://app.smartsheet.com/b/publish?EQBCT=5bcb0971d78f4808bf67083892dbd01c

Sean Flannagan · 07/07/24

This is an insightful solution. The challenge will be that each year, I'd just have to update the checked columns, but I'd have to do it for every employee. I would like something more automated. Is there something that would run the last 52, 26, or 12 weeks?

jmyzk_cloudsmart_jp · 07/08/24

Hi @Sean Flannagan

First, I used the following method to determine if a date is the last 52, 26, or 12 weeks.

Get the [First Day of the Week] (of today) (a summary sheet field)
Check if the date to examine is >= [first day of the week] - 7 x 52 (last 52 weeks)

The formula for each is as follows;

[First Day of the Week]# =TODAY() + IF(WEEKDAY(TODAY()) = 1, -6, 2 - WEEKDAY(TODAY()))
[Last 52 Weeks] =IF(Date@row >= [First Day of the Week]# - 7 * 52, 1)

Explanation

Determine the Start of the Current Week
Use TODAY() to get the current date.
Adjusting this date to find the start of the current week.
The formula = TODAY() + IF(WEEKDAY(TODAY()) = 1, -6, 2 - WEEKDAY(TODAY())) ensures that you get the first day of the current week (typically Monday).
Calculate the Threshold Date
To find if a date falls within the last 52 weeks, calculate the date 52 weeks (364 days) before the start of the current week.
The formula [First Day of the Week]# - 7 * 52 effectively shifts the date back by 364 days.
Compare Dates
For each date you want to check, compare it against the threshold date. If the date is greater than or equal to the threshold date, it falls within the last 52 weeks.
This logic can be adjusted for different time periods (like 26 or 12 weeks) by changing the multiplication factor (e.g., 7 * 26 for 26 weeks).

https://app.smartsheet.com/b/publish?EQBCT=2266781c98bb4c65890a5be18155aefa

Applying to All Employees

"To do it for every employee": If the employee's email or contact information is in the contact list column, you can use the current user filter of a report.

As you can see from the image below, the report shows only rows with me as employee column values in the sheet above. If app@cloudsmart.jp is the current logged-in user, the report will show only the app's rows.