TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
Weekly reports
My company does weekly reports. If we could group data by WEEK this would be great.

John Walsh
Date Created
11/19/2010 9:31:21 AM
Date Updated
11/30/2010 1:11:00 PM
New Idea
Promoted By
John Walsh
Sam Parish 11/19/2010 1:06:56 PM
John, if someone hasn't already helped you, there are numerous ways it can be done. I have made complicated reports and dashboards that view, sort and organize payroll reports, as well as cost quotations, job costing and profit projections. Please dig in, you will be pleased.
Kirill Bondar  Staff  11/24/2010 9:51:50 AM
We do not group by week since the week starts on different days in different countries. While start day can be calculated from user's settings, it may introduce inconsistency with reports: users from different countries may see different date.

However, TeamDesk introduces periods.

You may consider week a period of 7 days starting on Sunday/Monday/any other day, and then calculate the beginning of the period. The view then groups by that date.

TeamDesk provides two functions for this:

FirstDayOfPeriod(<date>, <duration>, <start>) calculates the first day of the period that falls on specified date, starts on <start> date with selected duration.

In terms of week, it expressed as:

FirstDayOfPeriod(<date>, Days(7), Date(2006, 1, 1)), assuming the week started on Sunday, or
FirstDayOfPeriod(<date>, Days(7), Date(2007, 1, 1)), assuming the week started on Monday.

There is no magic in a last date parameter, it can be any date that falls on a start of a week.

There is also a LastDayOfPeriod() function - the counterpart to FirstDayOfPeriod().

To build the report, create a Formula - Date column that will calculate beginning of the week that falls on a date, and then group by this column.
Liquid Rapid 11/30/2010 1:11:00 PM
There's also this "get calendar week number" formula, from the TeamDesk sample formulas PDF:

Mod((FirstDayOfPeriod([Date Column], Days(7), Date(2000, 12, 31)) - Date(2000, 12, 31)) / Days(7), 52)

If you replace [Date Column] with the date value you want to check, this will give you a number from 1-51 which represents the week number of that date. You can then group or filter on week number.
Back to Search Results