Analytics Calculations: A few of the limitations

josephmckeownjosephmckeown Posts: 37Moderator, Lightspeed Staff moderator
edited June 2018 in Reporting & Analytics
As we venture out into the world of customized reports, there are some limitations to keep in mind for best results and for your emotional well-being.

Table Limits 5000 Rows

Analytics can only display 5000 rows of results,

An incomplete table with a calculated column will not let you do either of the following:
  • Sort by calculated results
  • Export more than 5000 rows of actual results.

Keep in mind that you can continue to sort by Dimensions and measures. The most common types of reports that may exceed their row limit will be Dusty Inventory or Customer Lifetime Value. 

You can sometimes avoid this by applying more filters to your reports: perhaps creating Dusty Inventory lists for only one Vendor or Category. Also limiting Customers by status, by State, or by "name begins with", etc...

Calculated Results are not included in Column or Row totals

This is primarily because it is not a given that calculated totals are meaningful. (Eg, the total of sales averages is not meaningful)

However, you can create calculations to show column or row totals, either explicitly as calculated fields of their own, or implicitly in complex calculations.

Lots of data can slow or break a report

There is a threshold to how much calculation a table can take before it doesn't launch any more. 

Enough calculations (including lots of rows and lots of columns), can slow down your browser

Currently, there is not an efficient way of illustrating when a report is too calculation-heavy, This is why, in the best practices, we recommend saving different versions of your report as the calculations become progressively more complex.

Value types don't always mix

In some calculations, Analytics does not like mixing types of values. This comes up a lot when dates are involved. In some spreadsheet values, dates are considered numbers. In Analytics they are not.

To compensate for this, you can extract a number from a date and use it instead.

You can only sort by one Dimension or Measure at a time

When sorting results, there is not a built-in way to create sub-sorting groups. Say I want to report on top sales by Month by Category for the past 5 months, I can sort by top sales by category for all months...

Or I can sort by months, but I will lose my top-dollar results

Now, to get the sorting I want, I can create a calculation hidden from visualization that will do the custom sorting for me.

Filters may filter more than you expect

If you're reporting on a dimension, and no data exists with that dimension on it, the value will not appear in your results.

This often occurs in Sales reports. If we are looking at quantity sold by Category in the past week, and we also add Quantity on Hand, our Quantity on Hand will appear low

This is not because the numbers are reported incorrectly, rather, it is only looking at the Products that sold in the past week, and providing us with a total of their Quantity on Hand, by Category

This is why it's good to measure Inventory levels from an Inventory report (such as "Dusty", with the Dusty filters removed), instead of a sales report

Calculations Home
Post edited by josephmckeown on
Joseph McKeown
Product Specialists, Post Sales
Lightspeed HQ
Sign In or Register to comment.