Custom Dimensions: Item or Matrix?

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 59 moderator
edited June 3 in Reporting & Analytics

Spoiler Alerts Below

Often, when running a retail store, we'll have some products prepared as Matrices, and other products, not.

When we're running Sales reports for Matrix products, we may not be interested in which size or colour sold, simply the style of product. For this kind of report, we could run a Sales report with a Dimension of "Matrix"

This is a good start, but we soon find a limitation: All the products that are not matrices are gathered into one null Matrix line.

However, if we try to find out what products these are, we need to add the Dimension of "Description"...

So this clarifies the non-Matrix products, but also separates the Matrix products.

Wouldn't it be nice if there was a way to gather Products together if they're part of a Matrix, and if not, not to.

(Look at that! What we're looking for almost completely lines up as an IF statement!)

Step One: Is the Product a Matrix?

We could create an IF statement to say: if a Product is a Matrix, return its Matrix value, if not, return its Description.

How can which Products are Matrix Products? Let's look at the data for a minute...

Products that are not Matrices have a null "Matrix" description.

Products are Matrices, have a Matrix description.

So let’s start by going to our Custom Fields, and creating a “new” Custom Dimension…

This should open up the Custom Dimension formula editor...


And to start, let’s just add the “is_null” function…

And then let’s look for the Matrix.

When we save the function and run the report again, we’ll get the new column which, if there is a “Matrix” description, will show “No”, and if there is no Matrix description, will show “Yes”.

Let’s save our report as new…

So now, let’s go back to our “Null Matrix” Custom Dimension, click on the gear icon, and duplicate…

And now, let’s open the "Copy" add around the calculation our if statement: If is_null is “No”...

then return the Matrix…

If is_null is not “No”, then return the Product Description…

So now, when we save and run, we should see the Matrix description where one is available, and we should a Product description if no Matrix is available…

Again, let’s save as new…

Step Two: A bit of Cleanup

Now that we have a Matrix or Description field, we can hover over the starter dimensions of "Description", select the gear icon, and "Remove"

Let's do the same for the "Matrix" and for our first "Null Matrix" Dimensions.

When we run the report again, it will gather the respective descriptions together...

If we want some additional clarity, we could make a duplicate of our "Null Matrix Copy"...

and replace the rules with labels:

This way, when we save and run our report

We'll also get identifiers about whether we are looking at a matrix grouping or a single product!

Spoiler Alerts! These are the Custom Dimensions formulae that we used today

Matrix or Item Name

if((is_null(${item_matrices.description}))=no,${item_matrices.description},${items.description})

Matrix of Item Label

if((is_null(${item_matrices.description}))=no,"Matrix","Description")

Calculations Home

15082

Post edited by josephmckeown on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.