Analytics Calculations: Change "Days Since Sold" to "Last Date Sold"

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 59 moderator
edited April 28 in Reporting & Analytics

Credit where Credit is due. This solution emerged as a result of a collaborative process with Analytics Product Specialist, and friend, Mark Running.


Sometimes, we may want to return on a report the date that a product was last sold.

This is not a difficult calculation to prepare from the Inventory Report, because "Days Since Sold" is a measure that is included, for example, on the Dusty Inventory report.

From our collection of Date Functions, we can use the Add_Days function to count backwards from the current day.

Remember that there are two arguments to Add_Date:

  • The first argument is the number difference.
  • The second argument is the date that you wish to adjust.

It will look something like this:

( difference, start date)

So, for our function, we want it to look like:

( negative days since sold, today)

So to start, let's start a new Table Calculation, and we'll look for the add_days function...

and we'll look for negative last day sold...

Now, if we use now() as our source date...

We get a timestamp with our days since last sold (this will not be the actual timestamp of the sale. Rather, it is the reflective timestamp of the current time.

So, to make this cleaner, as the second part of our function, we could convert now into a date, use the Date function.

Remember, the Date function requires three arguments: ( year, month, day), so to prepare the function, I'm going to add three sets of brackets, separated by commas

So we want to find the year, the month, and the day from now. To do this, we could use the extract_year, extract_month, and extract_day functions. I will drop one of these between each set of brackets...

...and then, what do we want to extract these times from? Well, from now. So I'm going to add the now() function into all three empty brackets...

So now, when we save,

We just get a simple date!

*Spoiler alert, the final calculation we used is:

add_days(-${cl_item_facts.days_since_sold},date((extract_years(now())),(extract_months(now())),(extract_days(now()))))


Calculations Home

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