Analytics Calculations: Pivot Functions

josephmckeownjosephmckeown Posts: 37Moderator, Lightspeed Staff moderator
Let's take a look at some of the functions that you can add to reports with Pivoted dimensions...

Lookup, Pivot Column, Pivot Index, Pivot Offset, Pivot Offset List



Lookup


a way of identifying where certain content on your table may be, and returning predefined results where it occurs. Requires three arguments, what we are looking for, what field we are looking in for it, and what to return if we find it.

Let's say for example, someone came to my store saying that it was their birthday last night. They are preparing their thank-you cards now, but had such a good time at the party that they don't remember who gave them what. A friend of theirs bought them a gift that cost $17.57 from my store, but they didn't have a receipt, they didn't know which friend of their it was, and they weren't sure which of my locations they bought it at.

So, I could start my table using a dimension of customer, pivoting a dimension of multi-store location, and using a measure of sales total...


then for my lookup function, I would look up the number 17.57...


for the second argument, I would ask it to look at the sale line total...


and then for the third argument, let's just Analytics to show us the name of the customer...


So my calculation looks like

lookup(17.57,${sale_lines.total_sales_no_tax},${customers.full_name})

and if I have prepared it properly, when I save



a-ha! It's finding a match to a customer in my third store!

I could perhaps create a copy of the calculation, but instead of asking it to show me the customer, I could ask it to show me which row it's on...


It's found a match in Row 151!




Pivot Column


Return the number of the column, ascending from Left to Right.

Requires zero arguments.


my calculation looks like:

pivot_column()

and if I have prepared it properly, when I save...





Pivot Index


Positioned at the far right of your table, returns the content from column X. Requires 2 arguments, which dimension or measure you wish to look at, and at how many columns into your pivoted results.

For example:


My calculation looks like:

pivot_index(${sale_lines.total_sales_no_tax},1)

and if I have prepared it properly, when I save...






Pivot Offset


Returns the content from x columns to the right. Requires two arguments: which dimension or measure you wish to look at, and how many columns to the right you want to look. Returns null when table runs out of columns

For example:



In this instance my calculation looks like:

pivot_offset(${sale_lines.total_sales_no_tax},1)

and if have prepared it properly, when I save...






Pivot Offset List


Creates a list of values in the same row, requires three arguments, which measure you wish to look at, at how many rows to the right you wish to start the list, and how many rows to the right you wish to include on the list.

For example...


In this instance, my calculation looks like:

pivot_offset_list(${sale_lines.total_sales_no_tax},0,3)

and if I have prepared my calculation properly, when I save



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