Products Grid: Adding a column 'nb of sales from dd:mm:yyyy to dd:mm:yyyy'

Target

We would like to add a column displaying the number of sales for a specific period, here 01/07/2018 to 31/12/2018

We will then be able to:

- use filters in the grids to find the most sold products and eventually remove unsold products

- export quickly the products grid including this field to work on an Excel spreadsheet

Setup

To add the field to the list of available fields for your products grids, click on the in the 'Add a field' panel and enter the following information:

What is the field ID?: nb_month_2020

Sc creates the field, you now need to populate the grid with:

Field name: july20_dec20
Table: Another table
Type: just displayed
Refresh combinations: No

From the Advanced Properties panel on the right handside:

- select the menu SQLSelect and enter:

return ',(SELECT SUM(od.product_quantity) AS nb
            FROM `'._DB_PREFIX_.'order_detail` od
            INNER JOIN `'._DB_PREFIX_.'orders` o ON (od.id_order = o.id_order)
            WHERE od.product_id = p.id_product
            AND o.valid=1
            AND o.current_state IN (4,5)
            AND o.date_add >= ("2020-07-01 00:00:00")
            AND o.date_add <= ("2020-12-31 00:00:00")
            LIMIT 1) AS nb_month_2020';

Here we look for valid orders with a status 4 or 5 (refer to PrestaShop > Orders > Status) for the period 01/07/2020 to 31/12/2020.

Exit the editing window.

The new field is now present in the list of available fields and you can add it to your product grids.