We would like to add a column displaying the number of sales for a specific year, here 2021
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
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_year_2021
Sc creates the field, you now need to populate the grid with
Field name: Year_2021
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 ',COALESCE((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 >= ("2021-01-01 00:00:00") AND o.date_add <= ("2021-12-31 00:00:00") LIMIT 1),0) AS nb_year_2021';
Here we look for valid orders with a status 4 or 5 (refer to PrestaShop > Orders > Status) for year 2019.
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.