Using Match+Index to compare different years' financial sales

If the store's financial year begins outside of January, sales trajectories can still be compared between the current and last financial years. You can add some calculations to Analytics to organize this.

1. Start by pulling up your sales by month for the past 36 months.

   

2. Next, locate the Sale Completed Month from your report, and change it to your Financial Month.

3. From the date functions discussion, you can use the add_months function. Add_months requires two arguments: how many months forward, and which months you're looking at. For example, if your financial year starts in May, you need to determine how many months remain until the following January. To reach January from May, you would add eight months. In this example, enter: add_months(8,) 

   

4. Then, the months you want to start from are the Sale Completed Month.

   

5. Save, and the report displays the redefined financial month.

   

6. Next, you want to read the results for a year. To start this, extract the year.

   

7. From the Financial Month, copy the entire calculation.

   

8. Paste the calculation into the Current Financial Year.

   

9. Click Save. The report displays the information as you need.

   

10. Finally, click Save As New.

    

Next, for example, if you want to use Analytics to view the details for the year 2019 in this model, you would need to adjust it for the next financial year. Instead of defining this year, what you can do is set the calculation for the highest year from the available Financial months.

1. Start by using the Large function from the Math functions.

   

2. Refer to the Financial Year.

   

3. Find the largest timestamp.

   

4. Click Save. The largest timestamp will be in position one.

   

5. Also, you could proactively create a new calculation to pull up the previous Financial Year.

   

6. Click Save, and the report will pull up the previous Financial Year.

   

7. Next, click Save As New.

   

Next, you can turn these years into dates using Date calculation. You can recreate dates that are somewhere on the list, and then organize them the way you want.

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

   

2. 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, and so on). Finally, you can define the day as the number 1.

   

   In this example image, there are two of these now: one for This Financial Year, and another for Last Financial Year.

3. Click Save, and the table for recreated years will read the results.

   

Finally, you have the framework to match and index the sales totals from the default sorting to your desired sorting. Start by matching the Financial Year-Month to the Financial Month.

1.  Firstly, build the calculation. 

   

2. Copy and paste the Financial-Year-Month calculation.

   

3. Then, copy and paste the first Financial-Month calculation. In this example, a second calculation to find the rows for the previous financial year is also included.

   

4. Click Save. The rows that correspond to our Financial Months for this Financial Year and the Last Financial Year display.

   

5. Using the match function, you can pull from the Sale Line Totals.

   

6. Enter each corresponding value for the financial year.

   

7. Click Save.

   

8. When you do it for both years, the values displayed are pulled from your report into the right order

   

9. Next, you can customize the visualization. Hide everything except the last two columns.

   

10. Then, copy the Last Financial Year calculation, but replace the -1 with -2

    

11. Then, click Save.

    

Analytics pulls up the corresponding months' results into the format that you want. There are many ways you can customize this report further, looking at running totals, looking at daily versions, and adding a goal for growth.