Excel Reporting
 
Library Overview Reference Products Images Sales Stock Departments Formal Stocktake Restrictions Customer/Account Day Of Week Time Of Day Weather

Excel Product Pinboards - Definitions

Definition pinboards return the data your have used to define a product

Basic Attributes

Pinboard names seperated by dots

  • product.description
  • product.plucode
  • product.unitprice
  • product.currentcost
  • product.supply.priority
  • product.supply.importance
  • product.publish

Pinboard names seperated by spaces

  • product description
  • product supplier rrp
  • product wholesale price N (where N is 1,2,3 or 4)
  • product size
  • product order message
  • product is polarised
  • product is mirrored
  • product placement id

Examples

FormulaOutputNotes
=pinboard("product.description", 1234)Tackle boxRetrieve the current description of product 1234
=pinboard("product size", 414)250gmCurrent descriptive size of the product

Product Departments

  • product department N id
  • product department N name

Examples

FormulaOutputNotes
=pinboard("product department 2 name", 1234)DiorGet Department#2 name for product # 1234
=pinboard("product department 2 id", 1234)17Get Department#2 internal id# for product # 1234
=pinboard("product department 6 name", 456)Plastic WareGet Department#6 name for product # 456
=pinboard("product department 7 name", 456)Get Department#7 name for product # 456. The output in this example indicates there is no department 7 assigned

Marketing Colours

  • product marketing color N

Extract the marketing color assigned to a product. The value of N can range from 1 to 4 (incl). Marketing colors contain names of colors and are typically used by eCommerce websites. The word color can be spelt either 'color' or 'colour', both are understood

Examples

FormulaOutputNotes
=pinboard("product marketing color 3", 123)Cherry RedThe word assigned to 'marketing color 3' for product 123

Simple Analytics

Sales Volumes and Revenue

  • product.sales.revenue.period
  • product.sales.revenue.notax.period
  • product.sales.cost.period
  • product.sales.count.period
    Sum of quantity for all saleline. If a single saleline has a quantity of 2, it is counted as 2. For non unit items this figure is in raw measurement units, so can result in quite large results.
  • product.sales.linecount.period
    Total number of salelines. If a single saleline has a quantity of 2, it is counted as one, as there is only one saleline.

Calculate sales totals for a single product

Arguments

ParameterNameNotesExamples
1PidRequired, with exceptions, see notes123
2Start DateOnly include sales after this date. If not provided, then yesterdays date is used is used1-jan-2020
today-180
3End DateOnly include sales up to this date. If not provided the default is to stop at midnight last night1-jan-2021
today-90
4LocationSelect a single location. The default is "0" which means all locations14
5Sales Type
6RestrictionsApply additional 'restrictions' to sales selection. See Notes

Restrictions provide additional filterig of the sales, the following restrictions are available.

KeywordDescriptionExample
kit
or combo
Select only salelines that were repriced due to a kit/combokit
markdown
or oneoff
Select only salelines that were sold using a markdown label
rewardSelect only salelines that were as a result of loyalty rewards being redeemed
supplier N
or spid N
department N
or depid N
teller NOnly sales completed by teller# N (From version P202)teller=16

N

Examples

FormulaOutputNotes
=pinboard("product.sales.revenue.period", 123)4056.92Total revenue for pid 123 yesterday
=pinboard("product.sales.count.period", 123, "1-jan-2021", "1-feb-2021",,,"markdown") 129Sum of all quantities, sold in Jan 2021, for Pid 123, that were sold due to a markdown price.
=pinboard("product.sales.revenue.notax,period", 123, "1-jan-2020", "1-jan-2021",,,"teller=16") 43218.06Total revenue for pid 123 for all of 2020, sold by teller#16

Purchased With

  • product.commonpurchase

Returns the product most commonly sold with a product, based on number of occurances, not quantity involved.

Arguments

ParameterNameNotesExamples
1PidRequired123
2Start DateOnly include sales after this date. If not provided the previous 90 days are used1-jan-2020
today-180
3End DateOnly include sales up to this date1-jan-2021
today-90

Examples

Sample Data

Sale#PidQty
801001231
80100171821
801011231
801011710
80101171822
FormulaOutputNotes
=pinboard("product.commonpurchase", 123)17182Pid 17182 is in 2 sales. While pid 17 has more quantity it is only in 1 sale
=pinboard("product.commonpurchase", 123, "year")17182Includes all sales this calender year
=pinboard("product.commonpurchase", 123, "year-1", "year")17182Includes all sales for the previous calender year

Strikerate

  • product.strikerate

The percentage of sales that included a given product. This is the number of sales the product appears in, regardless of quantity sold.

Arguments

ParameterNameNotesExamples
1PidRequired123
2Location Id
3Start DateOnly include sales after this date. 1-jan-2020
today-180
4End DateOnly include sales up to this date1-jan-2021
today-90

Examples

FormulaOutputNotes
=pinboard("product.strikerate", 123, 0, "1-jan-2020", "1-jan-2021")14Pid 123 is in 14% of all sales, all stores in 2020
=pinboard("product.strikerate", 123, 4, "1-jan-2020", "1-jan-2021")17Pid 123 is in 17% of all sales in store #4, in 2020