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:

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:

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:

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