Analytics Calculations: Totals by Varying timeframes 2

josephmckeownjosephmckeown Posts: 44Moderator, Lightspeed Staff moderator
edited June 2018 in Reporting & Analytics
Say we want to compare our results this month-to-date to where we were at this relative position last month, or this relative position the same month last year.

We could use calculations to retrieve these results for us in a meaningful way.

Let's start by opening up Recent Sales, then setting the filters to go back to the past 400 days...


Then to start with this month, let's go to our date functions and extract the month and extract the year...


...from the sale completed month...


...whence upon saving...



Let's also use the add_months function...


...to add -1 months to the sales date...


giving us an idea about the previous month...


Then, in one shot, let's extract the month and the year...


...from the the sales date one month ago...


so that when we save...



We get numbers for the respective years and months!

Finally, let's add a calculation to define last year, this is simple. It's this year, minus one.

Starting with brackets...


...copying "this year"...


...pasting...


...and saving...



And this brings us to a good spot to save our changes to the report so far...


Now, in one shot, from our table-based functions we're going to add FIVE indexes...


...all looking at row 1...


...and to these, we are going to ask Analytics to look at Row 1 for:
  • This Month
  • This Year
  • Last Month
  • Last Month's Year
  • Last Year
So let's copy the calculations from the respective fields above...


...pasting them into their respective fields...


...so that when we save, we have columns that reference all the data we need to create the dates


And let's save this as a new version of our report


Now we can build the build the dates that we want to arrange for this month, last month, and this month last year...

We'll start with this month, again using the Date function...


The first number we want is the year, so this will be the calculation we used to index "This Year" from row 1...


...which we'll copy, and then paste into the date calculation...


...next we'll need the month, so we'll grab the indexed "This Month" from row 1...


...and then paste into our date...


...now the last number we need is the day. To have the days rise in sequence, let's add Row()...


Now when we save...



We have a the first re-creation of this month's days. 

Let's save the report as new...


Next, we'll add a new date function...


And (fast forward), we're going to add the year from the indexed "last month's year", the month from the indexed "last month", and the day from the Row again...


so when saving we get a recreated sequence for last month...



and we'll save as a new report again...


Then we'll add a new date for this month last year, sourcing the indexed "last year", the indexed "this month" and the row...


...and save...



...and save...


So now we have all the dates we need to build our comparisons.


Do you feel like a coffee? I'll wait here if you need a minute. You've done a lot of hard work up to this point.


Ok cool. 


Now, this is unorthodox, but to facilitate this discussion, I'm going to remove all the calculations except our final dates...


Everything is implicit in our date calculations...



So they will still work all by themselves...


So from here, it becomes simple.

Let's add three calculations to match against the completed date...


One for this month...


and for last...


etc...


So that when saving...



We see where each date on our table occurs from our completed dates column...

And save.


Now, let's add three calculations to index from the sale line totals...


the positions that we produced in our matches...


...from our different respective months...


...and save...



...and save...


One last step with the calculations. From our math functions, let's now add three running totals...


one for each month...


...as we did prior...


and save...



...and save.


So that's it for calculations! The rest of our report is now cosmetic.

Let's remove all the calculations except our running totals...


Let's hide the "Completed Date" and "Sale Line Total" columns from our visualization...


Let's also limit our visualization, just to the first 31 rows of data...


and let's see what this looks like on a graph...



We're almost there, but it looks like we forgot something; labels...

My graph doesn't know how to measure the X or Y, so to make it simple, I'm going to add a "Day of the Month" calculation, just using Row()


So when I save...



That looks better.

And there we are!




Calculations Home
Post edited by josephmckeown on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.