Calculations

Most prices you need are already in the quote data: the grand total in conf_total, VAT in vat_total, and subtotals and totals in the BoM. When you need something else, such as a total incl. VAT, the sum of all accessories or a discount percentage, you can calculate it in the template.

Basic math

Formatter	Meaning	Example	Result
add(x)	plus	{d.conf_total:add(d.vat_total)}	516551
sub(x)	minus	{d.conf_total:sub(3761)}	512790
mul(x)	times	{d.conf_total:mul(0.25)}	129137.75
div(x)	divided by	{d.conf_total:div(12)}	43045.9166…
mod(x)	remainder	{d.id:mod(2)}	0

The value x can be a number, a full path (d.vat_total) or a path relative to the current object (.quantity).

Totals incl. VAT

Total excl. VAT:  {d.conf_total:formatN(2)} {d.currencyCode}
VAT:              {d.vat_total:formatN(2)} {d.currencyCode}
Total incl. VAT:  {d.conf_total:add(d.vat_total):formatN(2)} {d.currencyCode}

Result

Total excl. VAT:  516,551.00 DKK
VAT:              0.00 DKK
Total incl. VAT:  516,551.00 DKK

To calculate VAT yourself, for example 25 %:

VAT 25 %:         {d.conf_total:mul(0.25):round(2):formatN(2)}
Total incl. VAT:  {d.conf_total:mul(1.25):round(2):formatN(2)}

Formulas

Formatters accept simple formulas with +, -, * and /. Multiplication and division are calculated first. Parentheses are not supported:

Total incl. 25 % VAT:     {d.conf_total:add(.conf_total * 0.25):round(2):formatN(2)}
Monthly over 36 months:   {d.conf_total:add(d.vat_total):div(36):round(2):formatN(2)}

Tip

Always round a calculated value before formatting it. This avoids small rounding differences like 0.30000000000000004.

Numbers stored as text

Some values in the data are text rather than numbers, for example form_submissions[].price ("516551.00") and the value of input elements ("50"). Most formatters convert them automatically. For totals, add add(0) first to make sure the value is treated as a number:

{d.form_submissions[].price:add(0):aggSum:formatN(2)}

Totals over a list

Aggregation formatters combine all items in a list into one value. Use [] (empty brackets) instead of [i]. No rows are repeated, only the result is printed.

Formatter	Result
aggSum	Sum
aggAvg	Average
aggMin / aggMax	Lowest / highest value
aggCount	Number of items
aggCountD	Number of distinct values
aggStr(separator)	All values joined into one text
aggStrD(separator)	All distinct values joined into one text

Examples with the example quote

Totals over all selected options in the configuration:

Total of all options:     {d.form_submissions[0].form.data.elements[].options[].price:aggSum:formatN(2)}
List price (before discounts): {d.form_submissions[0].form.data.elements[].options[price>0].price:aggSum:formatN(2)}
Total discounts:          {d.form_submissions[0].form.data.elements[].options[price<0].price:aggSum:formatN(2)}
Most expensive item:      {d.form_submissions[0].form.data.elements[].options[].price:aggMax:formatN(2)}
Number of priced items:   {d.form_submissions[0].form.data.elements[].options[price>0].label:aggCount}

Result

Total of all options:     516,551.00
List price (before discounts): 594,841.00
Total discounts:          -78,290.00
Most expensive item:      398,870.00
Number of priced items:   14

Filter by product property:

Software total:     {d.form_submissions[0].form.data.elements[].options[productProperties.type='Software'].price:aggSum:formatN(2)}
Accessories total:  {d.form_submissions[0].form.data.elements[].options[productProperties.type='Accessories'].price:aggSum:formatN(2)}

Result

Software total:     56,580.00
Accessories total:  29,140.00

(In the example quote, the training package also has the product property type set to Accessories, so it is included in the accessories total.)

Combining text

Join values into one line of text:

Selected software: {d.form_submissions[0].form.data.elements[].options[productProperties.type='Software'].label:aggStr(', ')}

Result

Selected software: Pulse Function, PowerArc, PowerArc Pulse, Sequence Repeat, Weaving, Offset

Subtotals inside a loop

Inside a loop, an aggregation over a nested list is calculated per item. This prints a subtotal per form element:

Element	Subtotal
{d.form_submissions[0].form.data.elements[i, type='element-with-options'].label}	{d.form_submissions[0].form.data.elements[i, type='element-with-options'].options[].price:aggSum:formatN(2)}
{d.form_submissions[0].form.data.elements[i+1, type='element-with-options']}

Result (excerpt)

Robot                         398,870.00
Other accesories               19,460.00
Software for PowerSource       17,440.00
Software for Robot             39,140.00

Running totals

cumSum prints a running total in a loop:

Accessory	Price	Running total
{d.form_submissions[0].form.data.elements[label='Other accesories'].options[i].label}	{d.form_submissions[0].form.data.elements[label='Other accesories'].options[i].price:formatN(2)}	{d.form_submissions[0].form.data.elements[label='Other accesories'].options[i].price:cumSum:formatN(2)}
{d.form_submissions[0].form.data.elements[label='Other accesories'].options[i+1]}

Result

Torch bundle - ABIROB® W5F       13,760.00    13,760.00
UR12E/8L KIT FOR 3PE              2,830.00    16,590.00
Wear Parts Box - W500 - 1.2mm     2,870.00    19,460.00

Line numbers

cumCount numbers the repeated rows 1, 2, 3, …:

#	Description
{d.form_submissions[0].form.bom.tables[0].groups[0].rows[i].label:cumCount}	{d.form_submissions[0].form.bom.tables[0].groups[0].rows[i].label}
{d.form_submissions[0].form.bom.tables[0].groups[0].rows[i+1]}

Result

1   Robot
2   MIG - Machine
3   MIG - Machine

Storing results

Use set to calculate a value once, store it, and use it anywhere in the document. Stored values go in c. and the tag with set prints nothing.

{d.form_submissions[0].form.data.elements[].options[price>0].price:aggSum:set(c.listPrice)}
{d.form_submissions[0].form.data.elements[].options[price<0].price:aggSum:abs:set(c.discount)}

List price:     {c.listPrice:formatN(2)} {d.currencyCode}
Your discount:  {c.discount:formatN(2)} {d.currencyCode} ({c.discount:div(c.listPrice):mul(100):round(1)} %)
Your price:     {c.listPrice:sub(c.discount):formatN(2)} {d.currencyCode}

Result

List price:     594,841.00 DKK
Your discount:  78,290.00 DKK (13.2 %)
Your price:     516,551.00 DKK

Note

Place the set tags before the tags that use the stored value, and keep them in the same part of the document (body, header or footer) for predictable results. Stored names may only contain letters and numbers.

Example: savings banner

Combine a stored value with a condition to show a banner only when the discount is significant:

{d.form_submissions[0].form.data.elements[].options[price<0].price:aggSum:abs:set(c.discount)}
{c.discount:ifGT(10000):showBegin}
You save {c.discount:formatN(0)} {d.currencyCode} on this configuration!
{c.discount:showEnd}

Excel formulas

In Excel templates you can also use normal Excel formulas next to your template tags. Keep formulas and tags in separate cells, and use formatN on tags so Excel receives real numbers. See Excel templates.