Analytics Calculations: Match+Index 2: Setting Financial Year by Month

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 59 moderator
Say that my store's financial year does not begin in January, but I want to compare my sales trajectory this financial year over last financial year.

We can add some calculations to Analytics to organize this. 

Let's start by just pulling up our sales by month for the past 36 months...


and then we'll take the "Sale Completed Month" from our report, and change it to our Financial Month.

From the date functions discussion, we could use the add_months function. Add_months requires two arguments: how many months forward, and which months we're looking at.

Let's say the start of our financial year is May. What we need to do is add the months to May that make it January. If we think backwards, December needs one month added to be January. November needs two. May needs eight...


Then the months we want to start from are the Sale Completed Month


So when saving, we have the redefined financial month...


But we want to read the results as a year, to start this, let's extract the year...


...from the Financial Month. Copying...


...pasting...


...saving.



...and let's save our work so far as a new report.


Next, we could ask Analytics to look for the year "2019" in this model, but that would mean that we would need to adjust it next financial year. Instead of defining this year, what we want to do is look for the highest year from the available Financial months. 

We can do this, using the "Large" function from our Math functions.


We want to look at the Financial Year...


...find the largest...


which will be in position one. So when we save...


...also, we could proactively create a new calculation asking us to pull up the previous Financial Year


...so that when we save...



...and again, let's save this as a new version...


So now, let's turn these years into dates. Using our Date calculation, we can re-create dates that are somewhere on the list, and then organize them the way we want.

The Date function requires three arguments; the year, the month, and the day...


Let's copy the calculation for "This Financial Year" into the year position, use the row() function to define the month (month one will be row one, month two will be row two, etc), finally we can simply define the day as the number 1...


I've created two of these now, one for This Financial Year, and another for Last Financial Year.

Now when we save...


We have a table for a recreated year to read our results on.

So now, we have the framework to match+index the sales totals from the default sorting to our desired sorting.

Let's start by matching the Financial Year-Month (column 6 above) to the Financial Month (column 2 above)

We'll start building the calculation...


copy+pasting the Financial-Year-Month calculation...


and then copy+pasting the first Financial-Month calculation...




I've also added a second calculation to find the rows for the previous financial year.

Now when we save, we see which rows correspond to our Financial Months for this Financial Year and Last Financial Year


And then using the Match function, we can pull from the Sale Line Totals...


each corresponding value for our Financial year...


So when I save...


When I do it for both years, we can see the values being pulled from our report into the right order


From here, all we need to do is cosmetic. Let's see what this looks like on a visualization...


now, let's hide everything except the last two columns...



then just for fun, let's copy the "Last Financial Year" calculation, but replace the -1 with -2


When we save...



It pulls up the corresponding months' results into the format that we want!

*There are lots of ways we might customize this report further, looking at running totals, looking at daily versions, adding a goal for growth



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