# Writing Calculations

Calculations are Excel-like expressions that may include any number of data properties, whether they are default data properties from your products catalog, custom data (#) properties, store data (!) properties, or built-in dynamic fields. These calculations may be mapped to nearly any customizable property of your GoPlanogram site.

In addition to basic arithmetic operators, calculations can include a few pre-supported operator keywords. They can be Basic functions, or Aggregate functions.

You can tell an expression is a calculation if the button beside the expression input has the icon.

Click here to see examples of basic calculations, and here to examples of aggregate calculations.

#### Basic Functions

Basic functions are performed on a single product and take 1-2 parameters. They are:

• `Format(exp, format)`

Formats a given expression according to a specified format

• exp is a property expression (i.e. `Price - Cost`)
• format is a numeric font specifier string. Possible values include:
• `P` (format expression as a percent, i.e. 87.12 becomes 87.12%)
• `C` (format expression as currency, i.e. 50.15 becomes \$50.15)
• `Int(exp)`

Returns the integer value of a given expression, rounded down (the value of the expression minus any fractional component). For example, 1.75 becomes 1.

• exp is a property expression (i.e. `FixtureDepth() / Depth`)

The `+` operator is used when adding numbers. To combine values as words, use the `&` operator. Use the "`_`" character for spaces.

For example, for a product with a `Height` of 6 and a `Depth` of 4:

• `Height + Depth` evaluates as 10
• `Height & "_x_" & Depth` evaluates as 6 x 4

Sample calculations that could be used on a per-product basis:

Property Name Property Expression Notes
Size (H x W x D) `Height & "_x_" & Width & "_x_" & Depth`

Returns the size of a product, formatted as `Height` x `Width` x `Depth`

The `&` operator is used instead of the `+` operator for string concatenation. The `_` character is used for spaces
Profit `Format(Price - Cost), C)`

Returns the profit of a product, determined by subtracting `Cost` from `Price`

The `Format` function takes `C` as its second argument, ensuring the returned value will be formatted as currency
Margin `Format((Price - Cost) / Price, P)`

Returns the profit margin of a product, dividing the gross profit by the `Price`

The `Format` function takes `P` as its second argument, ensuring the returned value will be formatted as a percentage
Number That Fit `Int(FixtureDepth() / Depth)`

Returns the number of a given product that can fit on that product's current fixture (i.e. shelf, rack...)

The `Int` function ensures no fractional product is included in the returned value

The `FixtureDepth()` keyword is used, which is a dynamic field

Percent of Shelf `Format(Width / ShelfWidth, P)`

The `Format` function takes P as its second argument, ensuring the returned value will be formatted as a percentage

The `ShelfWidth` keyword is used, which is a dynamic field

Finish `#Finish` The use of the # prefix tells GoPlanogram to map to a custom data property from your products spreadsheet -- in this case, `#Finish`

#### Aggregate Functions

Aggregate functions are performed across all elements in a display, and are used in reporting. They are:

• `Sum(exp)`

Sums the result of a given expression for every item in the display

• exp is a property expression (i.e. `IsOnPegboard()`)
• `Avg(exp)`

Sums the result of the given expression for every item in the display, then divides by the number of items in the display

• `exp` is a property expression (i.e. `(Price - Cost) / Price`)

Sample calculations that could be used as aggregates:

Property Name Property Expression Notes
Total Display Price `Sum (Price * Int(FixtureDepth() / Depth))` Returns the price of the entire display, by getting the `Sum` of the `Price` of all products in the display. Note the use of `FixtureDepth()` and the `Int` function, which are used together to find out how many of each product could fit in this display
Total Display Cost `Sum(Cost * Int(FixtureDepth() / Depth))` Returns the cost of the entire display, much like the previous example, but using `Cost` instead of `Price`
Average Margin `Format(Avg((Price - Cost) / Price, P))`

Returns the average profit margin for the entire display by calculating each product's profit margin, using the `Avg` function to get their average, then passing `P` to the `Format` function to ensure the result is formatted as a percentage

Note this function assumes one product per facing. It could be expanded to use `FixtureDepth()`

Gross Margin `Format((Sum(Price) - Sum(Cost)) / Sum(Price), P)`

Returns the gross profit margin for the entire display by subtracting the `Sum` of the `Cost` of all products from the `Sum` of the `Price` of all products, then dividing by the total price of the display.

The `Format` function takes `P` as its second argument, ensuring the returned value will be formatted as a percentage

Note this function assumes one product per facing. It could be expanded to use FixtureDepth()

Total Weight `Sum(#Weight * Int(FixtureDepth() / Depth))`

Returns the total weight of all products in the display, by multiplying `#Weight` by the max number of products that fit in each facing, based on the depth of the fixture that facing is on.

The use of the # prefix tells GoPlanogram to map to a custom data property from your products spreadsheet -- in this case, `#Weight`

Items on Shelf `Sum(IsOnShelf())`

Returns the total number of items on a shelf in the display by use of the Sum function and the IsOnShelf() dynamic field

Items on Pegboard `Sum(IsOnPegboard())`

Returns the total number of items on a pegboard in the display by use of the Sum function and the IsOnPegboard() dynamic field