Skip to main content

Work with numbers and math functions

Use rule boxes to work with numbers and math functions in Productsup.

Introduction

Use rule boxes to work with numbers and math functions from Data View in the platform, such as creating discounts, using formulas to calculate Return on Advertising Spend (ROAS), or calculating and managing your numeric data. Rule boxes also let you create random numbers.

Note

When working with numbers, ensure they are valid numbers, either integers or floats and don't contain any text elements, for example, commas. Consider using the Make Valid Price rule box to normalize the numbers. See Validate prices and convert strings to integers.

Generate random numbers

Generate Random Number rule box

To add a random number as a value in an attribute, you can use the Generate Random Number rule box.

You can add a rule box in Dataflow and Data View. Here are the steps to add this rule box in Data View:

  1. Go to Data View from the site's main menu.

  2. Choose the needed export channel or the intermediate stage in the drop-down menu on the left.

  3. Select Edit in the attribute's column where you want to apply the rule box.

  4. Choose the Select an Option drop-down menu.

  5. Search for and select the Generate Random Number rule box.

    generate_random_number_rule_box.png

  6. Select Save. The rule box overwrites the current values of the edited attribute with randomly generated numbers.

Generate MD5 Hash rule box

To generate a unique and encrypted MD5 hash for your attribute's values, you can add the Generate MD5 Hash rule box.

You can add a rule box in Dataflow and Data View. Here are the steps to add this rule box in Data View:

  1. Go to Data View from the site's main menu.

  2. Choose the needed export channel or the intermediate stage in the drop-down menu on the left.

  3. Select Edit in the attribute's column where you want to apply the rule box.

  4. Choose the Select an Option drop-down menu.

  5. Search for and select the Generate MD5 Hash rule box.

    generate_md5_hash_rule_box.png

  6. Select Save. The rule box overwrites the current values of the edited attribute with MD5 hash values.

Generate Unique ID rule box

You can create a unique ID for each product using the Generate Unique ID rule box. This rule box generates the same ID for all products that have the same values in the edited attribute or any other chosen attribute within your site. You can also generate IDs for your products based on their values in multiple attributes so that products with the same values across all the chosen attributes have the same ID.

Tip

You can use the Unique ID Generator data service to create IDs for all the products within your site, project, or account. See Create unique IDs for your products with the Unique ID Generator.

You can add a rule box in Dataflow and Data View. Here are the steps to add this rule box in Data View:

  1. Go to Data View from the site's main menu.

  2. Choose the needed export channel or the intermediate stage in the drop-down menu on the left.

  3. Select Edit in the attribute's column where you want to apply the rule box.

  4. Choose the Select an Option drop-down menu.

  5. Search for and select the Generate Unique ID rule box.

    generate_unique_id_rule_box.png

  6. In Algorithm, choose what type of ID you need. It can contain letters and numbers or numbers only and be of different lengths.

  7. In Column, select what attributes the rule box should use as a basis for ID generation. For example, if two products have the same value in the chosen base attribute, the rule box creates the same ID for both products.

    Note

    If you don't select an attribute, the rule box follows the same logic using the current values of the edited attribute.

  8. Select Save.

Add VAT to prices

To add VAT to your prices, you can use the Add VAT rule box:

  1. You must enter the VAT percentage in the Adds VAT to a Price field.

  2. You can choose, to round, always round up, or round down the percentage.

add_vat_rule_box.png

Calculate discounts

You can calculate prices based on a set discount or calculate the discount percentage between the original price and the sale price with the Discount rule box.

Discount rule box

To calculate prices based on a set discount and when your products meet a specific condition, consider using the Discount rule box. This rule box lets you:

  1. Define a column based on your situation and add the condition in the Mandatory Column input field.

  2. Optionally, add a condition in the Optional Column input field.

  3. Enter the percentage of discount you want to apply in the Discount input field.

  4. Define what happens when a product from your current column does not contain a number.

calc_discounted.png

Note

If you want to apply a discount to all products, you can add a mandatory condition: price > 0.

Discount Percentage rule box

To calculate the discount percentage between two prices, you can use the Discount Percentage rule box. This rule box helps determine the percentage from the regular and sale price. With the Discount Percentage rule box, you can:

  1. Define the original value by selecting the applicable column in Old Price. Then choose the column containing the desired price in New Price.

  2. Use Round Precision to define how many decimal places you wish to calculate the percentage.

  3. Optionally, add text before or after your percentage calculation in Text Before and Text After.

calc_percentage.png

Compare numbers from two columns

To compare numbers from two columns and assign a value based on the comparison outcome, you can use the Numeric Compare rule box.

  1. You first select your first column for the comparison and choose the operator you want to use in the comparison drop-down menu.

  2. Select your second column for the comparison.

  3. Define the value you want to assign when the comparison finds a match in the handle match input field.

  4. Determine what happens when there is no match under the handle no match input field.

numeric_compare.png

Round numbers

You can round numbers using the Round Numeric Values rule box:

  1. Define how many decimal places you want to calculate the percentage under Round Precision.

  2. You can decide what should occur if your current input is an invalid number in the If column value is not a drop-down menu.

round_numeric_values_rule_box.png

Perform mathematics calculations

Math rule box

Using the Math rule box, you can perform mathematics calculations for a column, which involves either a static value or values from another column.

  1. Use the Operator drop-down menu to select whether you would like to add, subtract, multiply, or divide a value from the current column.

  2. In Column, choose a value you wish to use to perform the above operation from the drop-down list. Or, in Static Value, enter a static value as desired instead.

  3. Define how many decimal places you want to calculate the percentage under Round Precision.

    • Leaving the Round Precision field empty does not affect the outcome.

  4. You can decide what should occur if your current input is an invalid number in the If column value is not a drop-down menu.

  5. You can select the round method.

math_round_numbers.png

Column Math rule box

You can also perform calculations using multiple columns by adding the Column Math rule box.

  1. Use the Operator drop-down menu to select whether you would like to add, subtract, multiply, or divide a value from the current column.

  2. In Input Column 1, choose a value you wish to use to perform the above operation from the drop-down list. Or, in Input Static Value 1, enter a static value as desired instead.

  3. In Input Column 2, choose a value you wish to use to perform the above operation from the drop-down list. Or, in Input Static Value 2, enter a static value as desired instead.

  4. Define how many decimal places you want to calculate the percentage under Round Precision.

    • Leaving the Round Precision field empty does not affect the outcome.

  5. You can decide what should occur if your current input is an invalid number in the If column value is not a drop-down menu.

  6. You can select the round method.

Tip

The Column Math rule box first calculates the Input Column 1 or Input Static Value 1 and the second Input Column 2 or Input Static Value 2. Once this action ends, the rule box performs a calculation between the input data and the newly calculated value.

In the following example, you add 20 to the price column before adding and returning this calculated value to the current price column.

column_math.png

Math Template rule box

You can define your calculations using a formula with the Math Template rule box. Use up to four columns in this rule box, including the current input column. This rule box lets you use Twig templates. Twig is a template engine that lets substitute dynamic content into the template. See Enhance your product data with Twig for use cases and rule on how to create Twig codes.

You can use the following math operators:

  • Addition: +

  • Subtraction: -

  • Division: /

  • Multiplication: *

  • Calculating the remainder of an integer division: %, for example, {{ 11 % 7 }} is 4.

  • Raising the left operand to the power of the right operand: **, for example, {{ 2 ** 3 }} is 8.

  • Floor division: //, for example, {{ 20 // 7 }} is 2.

To add the Math Template rule box:

  1. Go to Data View from the site's main menu.

  2. Choose the needed export channel or the intermediate stage in the drop-down menu on the left.

  3. Select Edit in the attribute column where you want to apply the rule box.

  4. Select the Add Box drop-down menu.

  5. Search for and select the Math Template rule box.

    Math Template

  6. Select up to three additional attributes to use for your calculations in Optional Column 1, 2, and 3.

  7. In Twig Template, enter a valid Twig template for your calculation. Use >_ icon to get an AI-generated suggestion.

    Twig Generator

    Note

    • The Twig should begin and end with curly brackets and spaces: {{ calculation }}.

    • You can refer to the values present in the columns as value.

  8. Define how many decimal places you want to have in Round Precision.

  9. Select Save.

Twig lets you create complex formulas such as:

{{ (value / column1) * 45 + column2 }}

See the Twig Fiddle template to create and test your formulas.

Display numerals instead of in scientific (exponential) notation format

If your calculation results in a long number, the platform displays them in the scientific (exponential) notation instead of numerals with decimals and trailing zeros. For example, you may have 5.7E-3 in scientific notation, but you want to display 0.000057 instead.

You can use a Twig template to convert your calculation results into numerals only. The following example shows a breakdown of how the process works.

{{(currentValue/1000000)|number_format(9)|trim('0', 'right')}}

  1. Twig performs a calculation, current value divided by 1,000,000. For example, (currentValue/1000000). For example:

    57 / 1,000,000 = 0.000057

  2. Next, in this example, you define the format of your values by adding nine zeros (0) to the trailing end of the value - number_format(9). Defining the format means, Twig trims or removes any trailing zeros at the end of the value up to nine places:

    0.000057000

  3. Finally, tell Twig to trim zeros from the right going left - trim('0', 'right'):

    0.000057000 = 0.000057

You can illustrate values in a numeric format by adding a Twig template in the Math or Text Template rule box. See Math Template rule box for more information.

See the Twig Fiddle template to create and test your formulas.

In-row Math rule box

You can use the In-Row Math rule box to add or multiply the values in one column per product.

For example:

Input: 1,4,6

Operator: add

Output: 11

  1. Enter a character to separate strings or cell values in Delimiter. Common delimiters are commas (,), semicolons (;), quotes ( ", ' ), braces ({}), pipes (|), or slashes ( / \ ).

  2. In Operator, define how to calculate the values by choosing Add or Multiply from the drop-down menu.

in-row_math_rule_box.png
In this section: