Is it possible to group by a custom week on Pivot Table?

Question

I have a table with {date_approved} column, now I want to summarize the count for this per week.
For example:
{date_approved} | {count}
06/01/2020 | 5
06/02/2020 | 5
06/03/2020 | 5
06/04/2020 | 5
06/05/2020 | 5
06/06/2020 | 5
06/07/2020 | 5
06/08/2020 | 5
06/09/2020 | 5
Let’s say 06/01/2020 and 06/08/2020 is Monday. 06/07/2020 is Sunday.
Total count: 35 (Monday to Sunday)
At present, this block is only summarizing the count per week, Monday to Sunday.
But I it to count/group from Friday to Thursday by default.
Is this possible? If not, what should I do to get the same effect? I just want to get a summary of {count} each custom week range {date_approved}.

Thanks!

ScottWorld · Accepted Answer

Welcome to the community, @Rue!Unfortunately, you can’t change the “bucket” functionality to use Monday as the starting day of the week, so that feature is out.The best you can really do in Airtable is create a new formula field in your table that results in the Current Week Number. The function WEEKNUM lets you specify what you consider the starting day of your week to be.So, this would be your formula:WEEKNUM({Date Field},"Monday")Once you create that formula, all of your records will now have a number, representing their week number of the year, if every week started on Monday.So now you can use that new “Week Number” field in any of your charts or graphs, including the pivot table block or even the normal chart block.The only real issue here is that now you’ll see that week number on your charts, which doesn’t really make sense to a human reading it. Seeing “Week Number 44” on a chart doesn’t tell me much about when that week really is.But seeing something like “Week of 6/8/20” would make a lot more sense. I don’t have the time to dive into this formula right now, but you could make this sort of thing happen with a lengthier formula using the DATEADD and DATETIME_FORMAT functions.

Rue · Answer

Welcome to the community, @Rue!Unfortunately, you can’t change the “bucket” functionality to use Monday as the starting day of the week, so that feature is out.The best you can really do in Airtable is create a new formula field in your table that results in the Current Week Number. The function WEEKNUM lets you specify what you consider the starting day of your week to be.So, this would be your formula:WEEKNUM({Date Field},"Monday")Once you create that formula, all of your records will now have a number, representing their week number of the year, if every week started on Monday.So now you can use that new “Week Number” field in any of your charts or graphs, including the pivot table block or even the normal chart block.The only real issue here is that now you’ll see that week number on your charts, which doesn’t really make sense to a human reading it. Seeing “Week Number 44” on a chart doesn’t tell me much about when that week really is.But seeing something like “Week of 6/8/20” would make a lot more sense. I don’t have the time to dive into this formula right now, but you could make this sort of thing happen with a lengthier formula using the DATEADD and DATETIME_FORMAT functions.Hi @ScottWorld,This will actually work for what I am trying to make. You led me to the right solution.Thank you very much!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded