Custom Dimensions: Redefining the Size Convention

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 80 moderator
edited May 2019 in Reporting & Analytics

Let's say we're selling Clothing, and that we have prepared our products in Size/Color Matrices in Lightspeed.

Now perhaps we want to prepare a report that will show us Products Sold by Size.

What we could do is just start a new Sales Report, and as our Dimension, just use Item> Matrix> Attribute 2

(this is where the default "Size" convention lives in Analytics, alongside that of any custom matrices that you may have created in Retail)

Then, let's just add one measure: Sales Line Quantity Sold

So this will show us, of all our Matrix products, the totals sold by size. But, most stores (including ours, we're only human) will experience a few problems here.

First, most stores have not historically used any naming convention to their Matrix attributes, so sizes, over the course of time, may have been created as "L", "Large", "LG", etc

This means that similar products are not gathered together. In the example above, 547 "L" Products were sold, but so were 33 "Large" Products

Second, neither Lightspeed nor Analytics will sort sizes according to size. The closest is sorting Alphabetically: in which case we will get:

Large, Medium, Small, X-Large, X-Small

Both data challenges make reading the results, well, challenging.

So, can we make any easy changes that will help us make more meaningful sense out of this?

Yes we can.

Step One: Redefining one value of Attribute 2

In an earlier article, we looked at using Multiple If Statements to return results from logical tests. We could use Custom Dimensions to do the same thing with the Sizes that exist in our data.

To begin a new Custom Dimension, navigate to Custom Fields> New> Custom Dimension

This will open up the editor for our Custom Dimension calculation...

and let's start with a one-rule IF statement. Let's say, from my results above, that I want to qualify "Large" as "L"

So I'll start my IF statement:

and remember. A simple IF function is constructed as follows:

IF ( Logical Test, Results if True, Results if False)

So I'm going to say, IF Attribute 2 is "Large", "L"

and just for now, let's say if Attribute 2 is not "Large", just make null...

So when I save my calculation and run the report again...

I'll see here that "Large" returns "L"!

So now, let's change the results if false. I'm going to go back to my Custom Dimension, click on the gear icon, and select "Edit"

and now, instead of "null" being my results if not true, I will change the rule to being "Attribute 2"

So now my calculation will look like this:

and when I save the calculation and run the report again, I'll see something different:

The only rule I checked for is if Attribute 2 is "Large", all the other sizes that do not fit this criteria kept their original definition.

Step Two: Redefining multiple values of Attribute 2

Now that I know I can change the Size convention, I want to be able to do that for multiple sizes. So, we need add some more IF statements in our original Custom Dimension.

Just to be safe, I'm going to go to my "Redefined Size" Custom Dimension, click on the gear icon, and select "Duplicate"

This way, I'll get a copy of the Custom Dimension that I can work with (and maybe break), but fairly consequence free.

So for multiple IF statements, the logic follows this process

IF ( Logical Test 1, Results if True, IF ( Logical Test 2, Results if True, Results if Both Tests are False))

Clicking on the gear icon of the Copy, let's select "Edit"

Then we'll add some more rules...

and closing off our calculation with results if not true (Attribute 2), then closing brackets for every IF statement

So let's save this, and run this...

So our "Copy" function looks much cleaner than the first two. But there's an even better way to test.

Let's remove the first Dimensions,

and only leave the last copy of our Custom Dimension, and run.

Here are our results, the exact same data...

But now it's much more organized.

Let's save our work here as new

Step Three: Define a Sorting Convention

So now our data is cleaner and much easier to read. The only thing missing now is a meaningful sorting convention.

This too, can be done using a Multiple IF statement.

Let's start by creating a new Custom Dimension:

And now, we'll start an IF statement, looking at our Redefined Sizes Copy...

Beginning with the very smallest option...

and I'm going to define the sizes in increments of 10, (this way, if more sizes are added in time, I can position them between the existing values)

and then going up the Size scale...

So when we save and run...

Each size is now assigned to a numeric value, so when we sort by "Sorting"

We're getting closer to the report that we've been looking for!

Let's save our results as new

Step Four: A bit of cleanup

The only reason we want the Sorting dimension is to sort the meaningful dimensions. We don't need for it to be visible on our report.

So let's hover over its gear icon, and select "Hide from Visualization"

Now, if we plot this on a table or graph

It becomes the graph we wanted all along!

*Clever observers may have noticed that I pushed "Men's" and "Women's" sizes both to the Simple version of Size Conventions, keep in mind though, nothing is stopping us from doing similarly with the model type, pivoting perhaps on "Build"

To create something like...

Calculations Home


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