Analytics Calculations: Turning Reorder Points into a PO
josephmckeown
Posts: 45Moderator, Lightspeed Staff moderator
Sometimes Analytics may be used to prepare data for using in Lightspeed. This section of discussions touches on ways to use existing data to take further actions.
Let's say that we're using Analytics to follow Inventory and define action points. Maybe we used it to build the Dynamic Reorder Points report....
Now that we can measure what Products need to be reordered, and how many need to be reordered, we can have Analytics prepare the data for us to Import as a PO into Lightspeed.
First, we need to add a Product identifier dimension. The best to use is "System ID", as this is a unique ID in Lightspeed that is never duplicated...
Let's run the report again to include the System ID...
Now our report looks like...
So we have the data that we need to import a PO, but we need to make a few changes.
First of all, not every product on our list needs to be reordered. If we scroll down our list of Inventory reorder points, some have negative reorder amounts...
What we could do is use an IF statement to nullify Products that do not need to be reordered...
For example, if we are not reordering at least one of a certain product, make its System ID null...
Following our best practices, let's start our calculation with brackets...
...then copy the calculation from our Dynamic Reorder Points value (or whatever formula you may have designed to define your own reorder points)...
then paste it into our IF criteria...
saying that if this value is less than one, return a null cell. If not...
Return the System ID (which Analytics reads as ${items.system_sku}
So when we save...
We get a column on the right hand side that stops returning System IDs once the reorder amount is less than one.
(You may be asking yourself why certain products whose reorder amounts are 1 are being nullified from this report. These reorder points are a decimal below one but above .5. If you wish to include these in the report, we could create a calculation to nullify products where we are required to reorder less than 0.5)
Now, we could use the same calculation to return the amount to reorder if that amount is not less than one...
I'm going to title this calculation "Order Qty" as Lightspeed will automatically recognize this column header when we import this spreadsheet...
And again we'll copy the formula for determining the amount to reorder...
Paste it into the criteria...
and paste it into the results if not less than one...
So that when we save...
So now, let's save our work as a new report...
From here, all our report needs is cosmetic.
Let's start by hiding the dimensions and measures from visualization...
Because we only need them if their result is greater than 1...
let's also remove all except the last two calculations...
Their formulae are implicit in the final two calculations, so we can safely remove them from the table now...
And again, let's save this report as new...
From here, we can download the report...
and we'll select the option of CSV "with visualization options applied"
If we want to see what the downloaded file looks like, we can open it here...
Now, we can go to Retail, and create a new PO...
and when we save...
We'll see the option to Import...
...from here, we can select the file that we downloaded from Analytics, and then pull its products and order quantities onto the order in Retail!
Calculations Home
Let's say that we're using Analytics to follow Inventory and define action points. Maybe we used it to build the Dynamic Reorder Points report....
Now that we can measure what Products need to be reordered, and how many need to be reordered, we can have Analytics prepare the data for us to Import as a PO into Lightspeed.
First, we need to add a Product identifier dimension. The best to use is "System ID", as this is a unique ID in Lightspeed that is never duplicated...
Let's run the report again to include the System ID...
Now our report looks like...
So we have the data that we need to import a PO, but we need to make a few changes.
First of all, not every product on our list needs to be reordered. If we scroll down our list of Inventory reorder points, some have negative reorder amounts...
What we could do is use an IF statement to nullify Products that do not need to be reordered...
For example, if we are not reordering at least one of a certain product, make its System ID null...
Following our best practices, let's start our calculation with brackets...
...then copy the calculation from our Dynamic Reorder Points value (or whatever formula you may have designed to define your own reorder points)...
then paste it into our IF criteria...
saying that if this value is less than one, return a null cell. If not...
Return the System ID (which Analytics reads as ${items.system_sku}
So when we save...
We get a column on the right hand side that stops returning System IDs once the reorder amount is less than one.
(You may be asking yourself why certain products whose reorder amounts are 1 are being nullified from this report. These reorder points are a decimal below one but above .5. If you wish to include these in the report, we could create a calculation to nullify products where we are required to reorder less than 0.5)
Now, we could use the same calculation to return the amount to reorder if that amount is not less than one...
I'm going to title this calculation "Order Qty" as Lightspeed will automatically recognize this column header when we import this spreadsheet...
And again we'll copy the formula for determining the amount to reorder...
Paste it into the criteria...
and paste it into the results if not less than one...
So that when we save...
So now, let's save our work as a new report...
From here, all our report needs is cosmetic.
Let's start by hiding the dimensions and measures from visualization...
Because we only need them if their result is greater than 1...
let's also remove all except the last two calculations...
Their formulae are implicit in the final two calculations, so we can safely remove them from the table now...
And again, let's save this report as new...
From here, we can download the report...
and we'll select the option of CSV "with visualization options applied"
If we want to see what the downloaded file looks like, we can open it here...
Now, we can go to Retail, and create a new PO...
and when we save...
We'll see the option to Import...
...from here, we can select the file that we downloaded from Analytics, and then pull its products and order quantities onto the order in Retail!
Calculations Home
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Lightspeed Analytics and reporting consultant
Lightspeed HQ