Excel Reporting
Library Overview Reference Products Sales Stock Departments

Excel Sales Pinboards

Sales Baskets

Details about a shoppers basket contents. Basket is a term meaning each individual sale. For statistical purposes seperate sales that we strongly believe are the same customer may be combined. For example if sale #1 is to Bob for X,Y and Z, and then 30 seconds later there is sale #2 to Bob for J, these may be combined and treated as one basket when reporting. The reasoning being they either were prompted to buy something else, or are splitting sales for some reason

  • sales.basket.size
    Average number of items in a basket.
  • sales.basket.counts
    A list of actual number of baskets for each count size. How many baskets had one item, how many had two etc, progressively increasing the size in each result. See notes for example
  • sales.basket.samples
    Number of baskets (sales) used in calculations
  • sales.basket.size.stddev
    The value of 1 standard deviation from the median
  • sales.basket.size.min
    The smallest number of items in a basket
  • sales.basket.size.max
    The largest number of items in a basket
  • sales.basket.size.median
    The median number of items in a basket

The number of items is the quantity sold for unit based items. For variable measured items each saleline counts as quantity=1.

ItemQtyMeasured
Tin of catfood2Each item
Loose carrots752gmPer gram
Total Items32 tins + 1 line of carrots

Arguments
ParameterNameNotesExamples
1Start DateOnly include sales after this date. If not provided, the previous 31 days is selected1-jan-2020
today-180
2End DateOnly include sales up to this date. If not provided the default is to stop at midnight last night1-jan-2021
today-90
3RestrictionsApply additional 'restrictions' to sales selection. See Notes

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

KeywordDescriptionExample
store NOnly consider sales made in store N
customer NSelect only sales to customer N
teller NOnly sales completed by teller# Nteller=16
depid NSelect only sales that include at least 1 item from department N
spid NSelect only sales that include at least 1 item from supplier N
maxqty NExclude salelines that have a quantity > N. This can be useful to exclude wildly wrong sales that have excessive quantity and distort results.

Examples

FormulaOutputNotes
=pinboard("sales.basket.size")2.18Average number of items per basket in last 31 days
=pinboard("sales.basket.samples")4372Number of baskets evaluated