You can build custom calculations onto a Lightspeed Analytics report to get more personalized data.
Building custom calculations in a report
Building custom calculations can be used to measure data in a report, such as the quantity sold. For example, if you want to find the average sale per category this week:
- In Lightspeed Analytics, launch a Recent Sales by Category report (which should include the Top Level Category of sales, and measures of Total, Profit, and Margin).
Navigate to Sale Line from the left-side menu.
- To add the measure of Quantity Sold, under Measures, navigate to # of Sale Lines > Totals.
- Select Quantity Sold.
- Click Run in the top-right corner to pull the sale-line quantity per top-level category.
Above the Dimensions and Measures, click the Custom Fields dropdown, and select New > Table Calculation.
Click Table Calculation to open the table calculation editor.
To find the average sale total, you'll need to divide the total sales by the quantity sold by adding the two measures in the table, so you can build a calculation that references them by name. In the calculation field, enter Total to narrow down your search.
Select ${sale_lines.total_sales_no_tax}, and it will display in the calculation field.
Next, add the divide function by entering a forward slash symbol /. The calculation will still work if you add spaces between the columns and functions.
Complete the calculation and divide by Quantity Sold by entering Quantity in the calculation, to narrow down your search, and select ${sale_lines.unit_total}.
Click Save Table Calculations to display the saved table with your new calculations.
In the table, a new green column will display in the table titled Calculation 1. It displays the Sale Line total divided by the Sale Line quantity.
If you made any errors in your preparation of the calculation, you will get an error in the results. But there are messages in the calculation field that may identify the error. Common errors may involve missing columns, columns with titles that have changed, or formulae that have not been entered correctly.
Optionally, you can format your new custom calculation and change the numbers. For example, switching from Default Formatting to U.S. Dollars by clicking Calculations > U.S. Dollars.
- Optionally, to change the title name, enter a new name in the field above the table calculation editor.
- If you made any changes, click Save Table Calculations. On the table, you can click the row title and sort by calculated results.
Using this method, you can build Averages for any kind of dimension, such as Average sales by Vendor, Employee, Month, Store, and more. You can also enhance your report calculation customizations by adding pivots on averages and adding comparisons, or comparing Averages to one another over time.