Custom Dimensions: Qty Sold: Multi-Date Snapshot

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 68 moderator
edited September 2019 in Reporting & Analytics

Spoiler Alerts Below

Credit where credit is due: the following workflow emerged with the kind assistance and astute observations of my colleague Jordan Lalumiere-Cameron

Let's say that we want to compare a small timeframe of sales against a larger ones; perhaps looking at Qty Sold over the past 7 days, and over the past 30 days, and over the past 365 days.

We could use Custom Dimensions to define timeframes with which to gather sales together.

To begin, let's open up the Recent Sales Report.

Its default filters will be looking at sales from the past week. Let's begin by changing it from this:

to say, the past 366 days (this way, we will capture all the sales that we are interested in.

Now, we'll want to add a new Custom Dimension to our report. So, on the left hand side of Analytics, select the "Custom Fields" and New> Custom Dimension

This will open up the field for adding a new Custom Dimension

Step One: When is Now?

To begin, we want to determine when now is. Using our Date Functions, we could use the Trunc Days function to create a date...

and in our function, we are going to trunc days around Now()

When we save our Custom Dimension, we should get a new column that simply demonstrates today's date:

Step Two: How many days ago was then?

Now we want to see how old each day's sales results are.

To do this, we can use the Diff Days function, counting the days' difference between two dates

Let's add a new Custom Dimension, and begin the diff_days function...

Diff_Days requires two arguments, the start date, and the end date.

Our start date will be the Completed Date of the Sale. Let's start looking for it...

Here it is!

Our second argument will be the end date, which will be the trunc_days of now that we created before...

Now when we save and run, we'll see next to each date how many does ago each day was...

Let's save our work thus far, remember from our best practices to "Save as New"

Step Three. Sales from the past 7 days

Now that we have an aging number for each day, we could start grouping them together using an IF statement.

I'm going to start a new Custom Dimension, and we'll use it to start a simple IF statement.

Again, using our best practices: we'll start building our IF statement just using brackets. We'll say, if the day count is less than 8, then we'll qualify that that as "1) Past 7 Days", if not, for now, just null.

I'm beginning the label with the number 1 because we'll need a sorting convention once the dimension is complete...

Now, let's save this Custom Dimension, then go back to open our "Aging Days" formula...

We'll copy the formula...

Then paste it into our IF statement (now titled as well)

So now let's save the Custom Dimension and run the report...

So sales less than 8 days ago are included, sales that are 8 days old or greater are not...

Let's save the work we've done, remember, as new.

Step Four: other timeframes

But we want to do more grouping than just the past 7 days, we also want to see the past 30 and perhaps the past 90,

So let's go back to our "Day Groupings" formula...

and we'll copy the language from the first part of our formula...

and we'll paste it a few more times:

So let's define the second rule, if the count is less than 31, define as "2) Past 30 Days"

...and so on...

finally, we'll define the results if none of the first three criteria are met:

So I have dropped the spaces between the rules, and added two more brackets to my formula to complete the calculation.

Let's save it and run it...

Looking good! Let's save our work.

Step Five: Switch from Dates to Products:

Now, of course we're not interested in seeing when dates occurred in relation to now. What we want to see is how Product sales lined up in those groupings.

So, let's remove the "Completed Date" from the report.

we can do the same for the "Date Now" and "Aging Days"...

Such that we only have our groupings and Qty Sold (for example)

Now, let's pivot on the Day Groupings custom dimension...

and we'll now add the Dimension of Product to the report:

...and let's run it

and there it is!

Step Six: Gather Sums

There's one more thing we need to do to complete this report. Add the results from the columns to the left.


Because some of the sales that occurred 30 days ago also occurred 7 days ago.

This is easier than it looks.

We'll start a new Table Calculation

...where we create the sum...

of a pivot offset list...

of the quantity sold...

...beginning 3 columns to the left...

for four columns!

So when we save, we'll get each day grouping's complete totals

Step Seven: Hide the remaining raw data

As a final cosmetic step, let's hide the Quantity sold from visualization

This way, our final report will simply be the totals that we want!

Spoiler Alerts! These are the Custom Dimensions formulae that we used today

Groupings: if((diff_days(${sales.time_stamp_date},trunc_days(now())))<8,"1) Past 7 Days",if((diff_days(${sales.time_stamp_date},trunc_days(now())))<31,"2) Past 30 Days",if((diff_days(${sales.time_stamp_date},trunc_days(now())))<91,"3) Past 90 Days","4) Past 365 Days")))

Gathering Four Columns of Totals: sum(pivot_offset_list(${sale_lines.unit_total},-3,4))

Calculations Home


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