Custom Dimensions: Change week to Monday through Sunday

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 59 moderator
edited September 18 in Reporting & Analytics

Sometimes we may want to define a week, not as Sunday through Saturday, but rather as Monday through Sunday instead.

If we're looking at only one week, we could define that week as only the past seven complete days, and then run the report on Mondays, but what if we need to follow multiple weeks' worth of results?

We could use Custom Dimensions to regroup sales into other weekly groupings

(Spoiler alerts below)

Step One: Finding the Data we need

So let's start preparing our Custom Dimension by getting some raw data to play with. Let's open the Recent Sales report, showing us daily totals since Sunday...


Let's also add a few Dimensions: the Sale Week, and the Sale day of Week...

When we run, we'll get the data fields we're looking for:

Finally, let's get some more days worth of sales onto our report.

As a default, the report is looking at the past week, let's open up the report filters, and select the past five weeks...

So now we get five weeks' worth of days that we can test and play with:


Step Two: Thinking about organizing days into weeks

Analytics labels a week as the Sunday in which it began. So: if a day of a sale is Sunday the first, the week of the sale will also be Sunday the first. If a day of the sale is Monday the second, the week of the sale will be Sunday the first

We could think about the timeframe like this:

  • Sunday 1 - Sunday 1
  • Monday 2 -Sunday 1
  • Tuesday 3 - Sunday 1
  • Wednesday 4 - Sunday 1
  • Thursday 5 - Sunday 1
  • Friday 6 - Sunday 1
  • Saturday 7 - Sunday 1
  • Sunday 8 - Sunday 8

In the default model, we gather days 1-7 together, however, in the Monday-Sunday model, we want to group days 2-8 together.

In the model above, we want Sunday the 8th to be included with the days from Sunday 1st, (we just wish not to include Sunday the 1st)

Step Three: Changing "week" if Sunday

An easy way to identify whether a sale date is a Sunday is if it is the same day as the week. In the model above we have:

  • Sunday 1 - Sunday 1
  • Sunday 8 - Sunday 8

So let's start with a simple rule:

If the day is a Sunday, push its "week" back by 7 days. If not, leave it in the week it is in, this rule would give us the following

  • Sunday 1 - Sunday -6
  • Monday 2 -Sunday 1
  • Tuesday 3 - Sunday 1
  • Wednesday 4 - Sunday 1
  • Thursday 5 - Sunday 1
  • Friday 6 - Sunday 1
  • Saturday 7 - Sunday 1
  • Sunday 8 - Sunday 1

Let's open a new Custom Dimension,


and we'll start with a simple test: the day of sale...


is equal to...


the week of the sale.


Let's save it and run.

As we expected, when the "day" is Sunday, the Custom Dimension returns as "Yes", when it is not, it returns a "No".

So far so good.

Now, we want to return an action, so let's duplicate our "Same day" argument...

and we'll edit it...

building an if statement around it...

So if the result is true, we want to change the week to the previous week. How do we do that? From our date-based functions, we could use the add_days function to add 7 negative days...

...to the completed week...


If the result is false, we can keep the sale week the way it is...

So let's save and run.

and there it is! The desired days are now qualified in the same grouping.


Step Four: Clear Labels

But of course, we're not finished. The "Week" of Sunday the 18th is not actually Sunday the 11th.

Now that Sunday is no longer when the Week begins, we need to change the dating convention of our new week. Our "Weeks" now all begin on Monday.

Fortunately, this is also an easy thing to change.

Let's duplicate the "Last Week if Sunday" calculation:

...and edit the copy...

and we'll build a new function around this...

Again, we will add_days

This time, just 1...


So when we save and run,


Our new weeks all begin on Mondays!

Step Five: Cleaning the date

When we use the add_days function, it adds the time stamp to the illustrated results.

We could trim the time stamp away from the label, by using the trunc_days function.

So let's duplicate the Monday Week

...edit the copy...

...and we'll add the trunc_days function around the calculation

...which will look like


Now, when we run and save

We get a clean and meaningful label!

Step Six: A bit more cleanup

So we have our label. Now we can remove all the starter data that we used to build our report.

The completed date...

...the last week if Sunday...

...and so on...

So when we have just the "Monday Week Clean" label left, let's run...

and there is it!

N.B.

Changing the convention of how the week is demonstrated will not change how Analytics is filtering on the week. Thus, as default, the first "week" on our report here is only showing you sales for this week. If show complete weeks is what you are hoping to do, for best practice, continue to filter reports by complete days in groupings of seven, and then run the report on Monday.



Spoiler Alert, these are the Custom Dimension formulae we used today

Week Starting Monday (Clean)

trunc_days(add_days(1,if(${sales.time_stamp_date}=${sales.time_stamp_week},add_days(-7,${sales.time_stamp_week}),${sales.time_stamp_week})))

Calculations Home

18552

Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.