Custom Dimensions: Comparing Month-to-Date to same Month-to-Date last year

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 80 moderator

One of the ways to illustrate our store's performance is to compare our trajectory this month compared to that of the same month in years past. However, because previous months are complete and the current month is not, we may not get as accurate a comparison as we are looking for. (Spoiler Alerts below)

However, we can use Custom Dimensions to identify which days' sales in years past should be considered in relation to this month.

This process is a bit different than the one that we look at in Relative Date of the Year, but the end goal is the same: identifying the sales from last year that align with this month's sales-to-date

Thinking about which sales to include

Let's say that today is May the 28th, 2019. So far, for this month, Analytics will show us Sales from May 1st through May 27th. To compare our trajectory, we may want to look at Sales for May 1st through May 28th 2018 (or further back, etc)

This is actually not a complicated report to prepare. There are two rules that we want to apply to all Sales so that their results can be measured against this Month-to-Date.

Rule 1: We want the month of the Sale to be the same as the month now.

Rule 2: We want the day of the Sale to be less than the day now.

Let's open up a Recent Sales report and let's remove the filters for this week...

Step One: What is the Month of the Sale?

So let's go our Custom Fields, and a New Custom Dimension...

This will bring up the field to create our new Custom Dimension

Using our Date-Functions, we'll start with the extract_months function....

...when we add it to the editor, it will look like this:

...then, between the brackets, we'll look for the field "Sale Complete Date"

When I add it to the editor it should look like this (title added to illustrate):

So when we save, run the report again, and our data should look like this...

So our sales in January should show up as a 1, sales in February as a 2, and so on...

Step Two: What is the Month Now?

Similarly, let's add a new Custom Dimension...

and start extracting the Month...

But now, we'll extract the month from, now.

So now, when I save and run my report again, I will see a second column that only shows me the number 5 (as "now", currently being May, corresponds to the number 5)

Let's save our report work as new...

Step Three: What is the day of the Sale, and the day Now?

Similarly to the month calculations in Steps One and Two, we also want to find extract the day from the Sale, as well as the day from now.

Let's add a new Custom Dimension to extract the day from the Sale...

...and a new Custom Dimension to extract the day from Now...

So when we save and run the report, we'll get two new columns, highlighting all our source numbers.

...and once again, let's save our report as new.

Step Four: Where are the Months the same?

So now, let's apply our first rule, the Month of the Sale is the same as the month it is now

If we go to our Custom Dimensions, let's start by hovering over the "Month of Sale" and selecting "Duplicate"

This will make a copy of the calculation that will make the next step easier.

On the "Month of Sale Copy", let's hover to get the gear icon, and select "Edit"

This will open up the editor with a copy of our original calculation:

So now, let's add =

...then add our calculation to extract the month from now.

So my calculation now looks like this:

extract_months(${sales.time_stamp_date}) = extract_months(now())

And when we save and run the report, we'll get a new column...

Where the month is the same, we'll get a "Yes", where it is not the same, we'll get a "No"

And let's save our work so far as new...

Step Five: Where are the Sales days less than the day now?

Similarly, let's go back to our Custom Dimensions, select the gear icon on "Day of Sale", and select "Duplicate"

...and in our copy, we'll add the second rule, is less than the day now...

so my calculation now looks like

extract_days(${sales.time_stamp_date}) < extract_days(now())

and when I save and run the report

We'll get the new column showing us "Yes" if the day is earlier, "No" if the day is not earlier.

Once again, let's save.

Step Six, where are both rules true?

So now, I have two simple rules that, when combined, will highlight all relative Months-to-date in years past.

Let's Duplicate the Custom Dimension for "Same Month as Now"

On the new Copy, let's select Edit

and then add the function: AND

then add a copy of the same calculation from our "Earlier Day" Custom Dimension

So now my calculation looks like...

extract_months(${sales.time_stamp_date}) = extract_months(now()) AND extract_days(${sales.time_stamp_date}) < extract_days(now())

...and when I save and run the report, I will get this final column:

If any of the first two rules is "No", then the Final column will be "No", but if both rules are "Yes", the final Column will be "Yes"

So let's save.

Step Seven: Applying the Custom Dimension

So now, we have illustrated that the Final Column is identifying the sales we wish to compare. However, we may not need any of these columns to be visible on our report.

So, let's remove the starter columns here,

(in truth, we could completely delete the starter columns from the Custom Dimensions completely, as all of the starter functions are now implicit in our final Custom Dimension, however, use with caution as once the Custom Dimension is deleted, there is no way to recover it)

So, now that we have dropped all the "started" details, let's say, pivot on the Sale Year

So now we can easily see, of all the sales from years past, which ones are comparable to this month to date?

We could also filter on our new Custom Dimension

Then perhaps only look at Sales by Brand in this time frame, year over year

and so on!

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

Sale Month is the Same Month as now

extract_months(${sales.time_stamp_date}) = extract_months(now())

Date is Earlier in Month than now

extract_days(${sales.time_stamp_date}) < extract_days(now())

Both Rules are true

extract_months(${sales.time_stamp_date}) = extract_months(now()) AND extract_days(${sales.time_stamp_date}) < extract_days(now())

Calculations Home


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