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 (performed on each product position), or Aggregate functions (performed across a collection of product positions).

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

Basic Functions

Basic functions are performed on a product position.

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

Basic functions include:

Format( exp , format , localization )

Formats a given expression according to a specified format.

exp A property expression (i.e. Price - Cost)

format The format specifier. Values include:

P (format expression as a percent, i.e. 87.12 becomes 87.12%)
C (format expression as currency)

localization A localization string, if formatting your expression as currency. If no value is provided, USD is assumed. Supported values:

eu Euro
gb British pound sterling
ja Japanese Yen
cny Chinese Yuan
kr Norwegian Krone

If( expressions , value_if_true , value_if_false )

Evaluates some given expression(s), then returns a given value if true, or a different given value if false.

expressions One or more expressions to evaluate for truthiness. You can use ||, &&, or () operators to compose multiple expressions.

value_if_true The value to return if the given expression(s) are true

value_if_false The value to return if the given expression(s) are false

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 A property expression (i.e. FixtureDepth / Depth)

Round( exp , places )

Returns the rounded value of a given expression. For example, 1.75 becomes 2.

exp A property expression (i.e. FixtureDepth / Depth)

places Optional. The number of decimal places to which to round. Defaults to 0 (i.e. round to the nearest integer).

Examples

Sample calculations using Basic functions:

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

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

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

FacingsTotal / !WeeklySales

Returns the decimal percentage of Weekly Sales the current product position is accounting for. For example, if the Weekly Sales of a product is 10, and we have five total facings at a position, this would be 0.5, signaling we are only meeting half the expected demand for that product.

The !WeeklySales variable is being sourced from some uploaded Store Data

The FacingsTotal keyword is used, which is a dynamic field

If(Profit > 30, passes, fails)

The If function takes Profit > 30 as its first argument, evaluating if a position's Profit is greater than 30.

If so, it returns "passes"; otherwise, it returns "fails".

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

Format(Profit / Sum(Profit), P)

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

The Profit keyword is used, which is a dynamic field

The Sum function is used, which is an aggregate function

#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:

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. Typically only used for report aggregates.

exp A property expression (i.e. (Price - Cost) / Price)

Sum( exp )

 
        Sums the result of a given expression for every item in the display. Typically only used for report aggregates.
        
 exp 
              A property expression (i.e. IsOnPegboard or Price - Cost)

 Examples
 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
Sum(Cost * Int(FixtureDepth / Depth)) Returns the cost of the entire display, much like the previous example, but using Cost instead of
Price
Format(Sum(If(Profit < 5, 1, 0)) / TotalProducts, P) 
  Returns the percentage of product positions who Profit exceeds the given value of 5.


  This is achieved through a combination of functions and dynamic fields.


  First, we calculate how many positions have a profit of greater than 5, with the If expression of
  Sum(If(Profit < 5, 1, 0))


  Then, we divide that by
  TotalProducts, a dynamic field.


  Then, we Format the result as a percetange, using parameter P.
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,
  Capacity,
  FacingsTotal, etc...
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,
  Capacity,
  FacingsTotal, etc...
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
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
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