Expand my Community achievements bar.

SOLVED

Is there a way to create a week dimension that is similar to the out of the box week dimension but reports Monday to Sunday?

Avatar

Level 4

Is there a way to create a week dimension that is similar to the out of the box week dimension but reports Monday to Sunday?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor and Adobe Champion

You can create a series of custom date ranges for this... 

 

I created 8 custom date ranges... "1 Week Ago (Custom Monday to Sunday)", "2 Weeks Ago (Custom Monday to Sunday)", etc

Most of our reports didn't look too much farther back, and with relative date ranges, this allows those 8 ranges to handle most of our needs.

 

Now, I should pre-face by saying that these won't work exactly like standard weeks, and they won't work with Line visualizations (since those are based off calendar granularities), but they can be used with Bar visualizations....

 

Here are the definitions I use:

 

1 Week Ago (Custom Monday to Sunday)

Jennifer_Dungan_0-1690234562798.png

 

Basically, using the "standard" week, I look at the start of this week (which would be Sunday, July 23) and subtract 6 days (to get the previous Monday - July 17th)

Then instead of using the default "End of" on my end definition, I switch it to start of current week (which is the same Sunday July 23 - midnight) and I add 1 day (to get it to include July 23). 

Jennifer_Dungan_1-1690234827012.png

 

 

2 Weeks Ago (Custom Monday to Sunday)

Similar to above, I use:

Jennifer_Dungan_2-1690234912028.png

 

Using the same standard week definition, I again apply my day shift...  minus 13 days for the start, and minus 6 days for the end, resulting in:

Jennifer_Dungan_4-1690235021066.png

 

You can follow the pattern of the 2 Weeks Ago range for any other weekly range, you just need to change the number of days to shift....

3 Weeks Ago - minus 20 days and minus 13 days

4 Weeks Ago - minus 27 days and minus 20 days

5 Weeks Ago - minus 34 days and minus 27 days

6 Weeks Ago - minus 41 days and minus 34 days

7 Weeks Ago - minus 48 days and minus 41 days

8 Weeks Ago - minus 55 days and minus 48 days

 

If you need more, just keep the math consistent with the above pattern, and if you are worried, just check the calendar preview.

 

 

Then you can use in your freeform like so (don't forget to sort by your dates ranges - which is also why I use "1 Week Ago" instead of "Last Week" since 1, 2, 3, etc of the X Weeks Ago allows for sorting):

Jennifer_Dungan_5-1690235548184.png

 

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor and Adobe Champion

You can create a series of custom date ranges for this... 

 

I created 8 custom date ranges... "1 Week Ago (Custom Monday to Sunday)", "2 Weeks Ago (Custom Monday to Sunday)", etc

Most of our reports didn't look too much farther back, and with relative date ranges, this allows those 8 ranges to handle most of our needs.

 

Now, I should pre-face by saying that these won't work exactly like standard weeks, and they won't work with Line visualizations (since those are based off calendar granularities), but they can be used with Bar visualizations....

 

Here are the definitions I use:

 

1 Week Ago (Custom Monday to Sunday)

Jennifer_Dungan_0-1690234562798.png

 

Basically, using the "standard" week, I look at the start of this week (which would be Sunday, July 23) and subtract 6 days (to get the previous Monday - July 17th)

Then instead of using the default "End of" on my end definition, I switch it to start of current week (which is the same Sunday July 23 - midnight) and I add 1 day (to get it to include July 23). 

Jennifer_Dungan_1-1690234827012.png

 

 

2 Weeks Ago (Custom Monday to Sunday)

Similar to above, I use:

Jennifer_Dungan_2-1690234912028.png

 

Using the same standard week definition, I again apply my day shift...  minus 13 days for the start, and minus 6 days for the end, resulting in:

Jennifer_Dungan_4-1690235021066.png

 

You can follow the pattern of the 2 Weeks Ago range for any other weekly range, you just need to change the number of days to shift....

3 Weeks Ago - minus 20 days and minus 13 days

4 Weeks Ago - minus 27 days and minus 20 days

5 Weeks Ago - minus 34 days and minus 27 days

6 Weeks Ago - minus 41 days and minus 34 days

7 Weeks Ago - minus 48 days and minus 41 days

8 Weeks Ago - minus 55 days and minus 48 days

 

If you need more, just keep the math consistent with the above pattern, and if you are worried, just check the calendar preview.

 

 

Then you can use in your freeform like so (don't forget to sort by your dates ranges - which is also why I use "1 Week Ago" instead of "Last Week" since 1, 2, 3, etc of the X Weeks Ago allows for sorting):

Jennifer_Dungan_5-1690235548184.png

 

Avatar

Community Advisor and Adobe Champion

Yes, you can do this... if it doesn't mess up other teams/reports... 

 

Changing the entire calendar configuration affects the entire suite, not just the team that wants a different breakdown... so a proper investigation into what the impacts would be to make such a change should be done prior to making the change.

 

Since the question was how to create custom definitions, I figured that changing the calendar definition wasn't what they wanted... as most people would phrase that question as "How can I make my weeks report Monday to Sunday instead of Sunday to Saturday"...