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 do not 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

To create a random number in a column, you can add the Generate Random Number rule box.

generate_random_number_rule_box.png

Generate MD5 Hash rule box

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

generate_md5_hash_rule_box.png

Generate Unique ID rule box

You can create a unique ID for each product using the Generate Unique ID rule box. This box generates the same ID for all products listed under your chosen column.

Alternatively, you can input columns you want the Generate Unique ID rule box to check for uniqueness when generating the ID.

generate_unique_id_rule_box.png

Optionally, you can define columns (newline-separated) for the rule box that should group products by creating a unique ID for each group. You can also consider using the Unique ID Generator service found in Data Services to achieve the same effect. See Data Service ID Generators.

Adding 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 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 completes, 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 for more flexibility. Use up to four columns in this rule box, including the current input column.

  1. Define up to three additional columns to use for your calculations with Optional Column 1, 2, and 3.

  2. Enter a valid Twig template for your calculation:

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

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

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

math_template.png

You can create complex formulas using Twig templates. The following example takes the current input value, value and divides it by the value in column1. It then multiplies the result by 45 and then adds the result to the value from column2:

{{ (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 decimal 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.

math_template_rule_box.png
text_template_rule_box.png

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: