Calculation Grids are intended for working with a large amount of row-like data (items) that are not tied to SKUs and therefore cannot be processed in Price Lists, Live Price Grids or Quotes. With Calculation Grids you are able to modify, execute and store data like supplier costs, promotions, etc.

Open

Key facts about Calculation Grids:

• A configuration wizard that helps you with the setup.

• Table keys can come from data, a calculation logic or can be configured manually.

• Table rows (line items) are generated by a dedicated calculation logic.

Calculation Grid List

On the Price Setting > Calculation Grids page, you can view and modify the existing Calculation Grids or create new ones.

After selecting a Calculation Grid from the list, the following actions are available:

• Calculate – Allows you to calculate a new Calculation Grid or recalculate an existing one if changes have been made.

• Delete the selected Calculation Grid. Note that the delete operation does not immediately remove all the line items and metadata associated with the Calculation Grid. A background job is run to wipe all these items and the whole process can take up to an hour.

Calculation Grid Detail

To open a Calculation Grid detail, click its ID or Label. The Calculation Grid detail table lists all items that the grid contains.

 The layout and column names of the Calculation Grid can be changed – for details see Customize Columns.

When no product is selected, the available options include:

• Add new record to the Calculation Grid (only available for grids with manual key type).

• Calculate (above the table) – Calculates the whole grid.

• Mass actions – In the Mass Actions dialog, you can perform various actions (edit, submit, withdraw, approve, deny or calculate) for all records at once or for a set of records defined by a filter with the possibility to add a comment.

When you select a product, additional buttons appear below the table:

• Formula Editor – You can add a new custom column and calculate its values using the values of the existing columns. The dialog displays all the fields available for your calculation and the result of the formula for the first line item. The Formula Help panel lists the supported functions. Note that the new columns are created only on the client side, which means you cannot sort or filter by them and they will not appear in an Excel download, Excel Client or any other integration request (JSON API).
  For example "ARCP + 11", to edit/view the formula right click the field heading and you get a drop-down menu where you can add a new formula (if never added) or edit an existing formula on that field.

• Delete one or multiple selected items.

• Calculate only the selected items.

• Submit / Approve / Deny the selected item. You can also use icons available at the end of the row.

In columns, where it is allowed by calculation logic (selected in the Set Parameters step), you can manually override the calculated results.

Create a Calculation Grid

1. Go to Price Setting > Calculation Grids and click the New Calculation Grid button.

2. In the Define Calculation Grid step:

   Open

   

   1. Enter a Label.

   2. Optionally, set a Target Date (fixed date) or a Relative Target Date (relative to the current date, e.g., setting -5 means the system will consider Company Parameters which are valid at current date (= date of calculation of LPG) - 5 days). This target date will be used in the price calculation.

3. In the Select Keys step:

   1. Select a Key Type:

      • Data Driven – Select up to six keys. The result data table will be automatically generated based on the number of keys and the selected restricted values.

      • Logic Driven – Keys are generated by a calculation logic. Select a calculation logic (of the Calculation Grid Keys nature) and the element containing the keys. After selecting a calculation logic, other inputs generated by the logic may appear.

      • Manual – Select up to six keys. An empty result data table will be created leaving it up to the user to populate it manually.

   2. For logic and data driven types, select the Dynamic Key Mode. Keys can be added or removed from the Calculation Grid at any new re-calculation of the grid based on the filter definition and the mode setup.

      • Add Only – Adds keys from the source data. Existing keys that are not present in the source data are preserved.

      • Add & Remove – Adds keys from the source data, existing keys that are not present in the source data are removed.

   3. For data driven and manual types, restrict the values either by a manually entered list or by a data source (reference a data table and its field). In data-driven grids, all the restricted values will be used in data table generation, in manual grids, users will be able to select only from the restricted values when manually adding a record.

4. In the Set Parameters step, select a calculation logic (of the Calculation Grid nature) that will calculate the grid.

   After selecting a calculation logic, other inputs generated by the logic may appear.

5. In the Output Elements step, select the calculation logic elements whose results you want to have as columns in this Calculation Grid. You can select up to 100 elements.

   • Hide in item list – If checked, the given element is not displayed but still available for mapping to an attributeXX field (for example, for the purpose of integration or filtering from API). However, it is still possible to see the values in the remote procedure call requests data (as it is still part of the data).

   • Change triggers approval – Forces an approval on a Calculation Grid line item when there was a change in underlying data (even if the result is the same).

6. Click Finish.

Calculate the Calculation Grid

When you have a new Calculation Grid ready, select it in the list and click the Calculate button.

In case of a manual Calculation Grid, open it and populate it using the Add new record button. For each row, select the values for the defined keys, then recalculate the whole grid.

The Status of the Calculation Grid will change from Draft to Pending, then Processing and finally Ready. It is necessary to refresh the list by clicking the Refresh icon in order to see the status change.

If needed, you can cancel the running calculation of a Calculation Grid (when it is in the Pending or Processing state). Click the Status and cancel the calculation in the Job/Task Tracking dialog.