Analytics Calculations: Pivoting

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 32 moderator
edited June 11 in Reporting & Analytics
Pivoting data is way of reporting on two major dimensions at once, by turning a list of results into a table of results.

In this example, we are looking at two dimensions of sales from the past 4 months, the first dimension being "Top Level Category", and the second dimension being "Completed Month".

When we add two dimensions, it creates the list of data...


...but when we pivot on the "Completed Month" and then run again...


.our results are displayed on a much clearer table 


Our data is identical, but it is easier to read quickly.

On the "Data" tab, we can also now select checkboxes for "Totals" (Column totals), and "Row Totals"


this will add space onto our table for totals...


so that when we run again...

we get summary results to measure categories, and months, and the overall total...


The most common kinds of pivots are reading results over time, day-over-day, month-over-month, year-over-year etc (as in the example above, or looking at sales or inventory activity in Multi-Store environments...


Just like dimensions, we can add two layers of dimensions to pivots...


Calculations can respond to pivoted dimensions, just as non-pivoted dimensions. Say for example, I wanted to highlight results from just one store, I would prepare it no differently than I would any other calculation...


in this instance, my calculation looks like:

if(${shops.name}="name82373","We Love Disco",null)

and if I have saved it properly...



Similarly, I can prepare compound IF statements looking at either pivoted or non-pivoted dimensions...


So in this instance, my calculation looks like:
if(${shops.name}="name82373","We Love Disco",if(${cl_category_tops.top_level_category}="CLOTHING","Time for Cookies",null))

and if I have saved it properly

 

Just like regular dimensions and measures, Pivoted measures have a special set of functions too...


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