Custom Dimensions: Relative Date of Year

josephmckeownjosephmckeown Posts: 44Moderator, Lightspeed Staff moderator
edited May 27 in Reporting & Analytics

One of the ways to illustrate our store's performance is to compare our trajectory this year compared to that of years past. However, because previous years are complete and the current year 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 year.

A good way to start is to identify the relative-date of the sale.

Thinking about which days to include

Let's say that today is May 27th, 2019. I want to compare my sales for January 1st through May 27th 2019 against the sales for January 1st-May 27th 2018 (and so-on in reverse).

To start, we want to plot all sales to a position against this year. This means that if a sale took place on May the 4th 2016, that we would align it with May the 4th, 2019.

Secondly, we want to plot where this years' relative sales are in relation to now. This means that if a sale took place on June 10th 2018, we don't want include it, yet.

Step One: The Relative Date

Using our date functions, we can use "Date" to create a relative date this year for all sales. We can use the "Extract Year" function to identify which year this year is, and we can use the "Now" function to identify the position in the year that we currently occupy.

So let's start by launching the "Recent Sales" report, and let's remove the filter for Sales Date is in the past 1 Weeks


Then let's open the Custom Fields...

and then select New> Custom Dimension

Remember that the "Date" function requires three arguments: the Year, the Month, and the Day

To plot all sales against this year, let's extract_year...

...from now...

To plot the month, let's extract_months

From the completed sales date...

...and let's do the same for the day...


So, our final "relative date" function should look like this:

date((extract_years(now())),(extract_months(${sales.time_stamp_date})),(extract_days(${sales.time_stamp_date})))

When we save...

The "Relative Date" should look at every sale date, whether it's a sale from this year or years past, and then make the year, this year.

Following our best practices. Let's save our work.

As new...

Step Two: Date Now

Of course, it is not necessarily the date of the sale that we want to measure itself. We want to compare the sales relative dates in comparison to now.

So, let's a new Custom Dimension...

...which will be a date function...

But now, we will extract the year, the month, and the day from now.

So our function will look like:

date((extract_years(now())),(extract_months(now())),(extract_days(now())))

So our report now looks like:

And saving As New


Step Three: Relation between Relative Date and Now Date

Now we want to look at the difference between the relative days of sales now.

To do this, let's add a new Custom Dimension...

...and we'll use the diff_days function to count the difference.

Diff_Days requires two arguments: the first date, and the second date

So, for our Start Date, let's copy the function for our Relative Date...


and paste...

and for our end date, let's copy the function for our Now Date, copying...

...and pasting...


So my final function for differences of days is...

diff_days((date((extract_years(now())),(extract_months(${sales.time_stamp_date})),(extract_days(${sales.time_stamp_date})))),(date((extract_years(now())),(extract_months(now())),(extract_days(now())))))

...and when we run the report, we will get a number counting the difference

Notice anything about this number?

If the relative day is after now, (or, later in the year) the difference is negative.

If the relative day is before now, (or earlier in the year) the difference is positive.

Let's save our work as new.

Step Four: Include or not?

So, can you guess what our final function will be to see if we should include it in our comparison or not?

That's right!

Is the difference of days greater than zero?

To start this, let click on the gear icon next to our "Difference Days" function and select "Duplicate"

This will create a "Difference in Days Copy" function that we can easily add our final test onto...

To do this, click on the gear icon on the "Copy" and select "Edit"

This will open up the copy of our function...

...on which we will add one final argument: "is greater than zero"

So my function now looks like:

diff_days((date((extract_years(now())),(extract_months(${sales.time_stamp_date})),(extract_days(${sales.time_stamp_date})))),(date((extract_years(now())),(extract_months(now())),(extract_days(now())))))>0

And when I save, it will look like:


So now, we have a new simple Y/N Dimension, was this sale earlier in the year in relation to today? THIS is the Dimension we'll be using for our analysis now.

Let's save our work.

Step Five: Pivot or Filter using this Dimension

Now that we have created the Custom Dimension "Is Earlier", we can remove most of the other dimensions from our data. After all, we may not be interested in reporting on the date itself. So for now, let's let's remove the "Completed Date"...


as well as our starter Custom Dimensions...

But when we get to "Is Earlier", let's, for now, Pivot on the results...

...and to illustrate, let's add a Dimension of the Sale Completed Year

So when we run, we can see a simple model

Showing us for each year, our relative Year-to-Date total!

A Few more Options

For additional analysis, we may wish to Filter on the custom dimension of "Is Earlier?"

and let's filter for "Is Earlier" is "Yes"

Giving us now one number to compare this year's totals to the trajectories of years past

Note: This is a really good time to use the Visualization of ...> Funnel

As it compares the top result as a percentage comparison to the other results...

We could also pivot on the year of the sale, and perhaps use a Primary dimension of Brand, to see how Brands are performing relatively Year-to-date


and so on...

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

Relative Date

date((extract_years(now())),(extract_months(${sales.time_stamp_date})),(extract_days(${sales.time_stamp_date})))

Date Now

date((extract_years(now())),(extract_months(now())),(extract_days(now())))

Difference in Days

diff_days((date((extract_years(now())),(extract_months(${sales.time_stamp_date})),(extract_days(${sales.time_stamp_date})))),(date((extract_years(now())),(extract_months(now())),(extract_days(now())))))

Is Earlier

diff_days((date((extract_years(now())),(extract_months(${sales.time_stamp_date})),(extract_days(${sales.time_stamp_date})))),(date((extract_years(now())),(extract_months(now())),(extract_days(now())))))>0

Calculations Home

14903

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