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

Restrict by Customer or Account

Some pinboards can be restricted by customer or account. This lets you see the sales figures to a specific customer or account

Both of these restrictions also understand the keywords "none" and "any", which return all sales without/with customer/account data

Restrict By Customer

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=14")
or
=Pinboard($O$1,$B$1,$D$1,$F$1,,,CONCAT("cid=",$A6))

When "none" is used on customer restriction, then all sales that have NO customer selected are included.

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=none")

When "any" is used on customer restriction, then all sales that have ANY customer selected are included.

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=any")

For a given pinboard, the value of "cid=none" + "cid=any" equals the pinboard value if you hadn't included any customer restriction. eg

    Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=any")
PLUS
    Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=none")

EQUALS
    Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023")

If you have a specific "cash customer" that is always selected, that for the purposes of reporting that is a "customer" and those sales will be treated using "Cash customer" as a valid customer. If can of course simply adjust by that count yourself using Excel, as shown below where we add "no customer" and "999999" (our 'cash customer') to get a total of all sales to cash or unknown customers.

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=none") + Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"cid=999999")

Restrict By Account

The account restriction checks if a charge account was available not if the account was used. That is, a customer with a charge account that pays using cash will still be counted as having an account

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"accid=14")
or
=Pinboard($O$1,$B$1,$D$1,$F$1,,,CONCAT("accid=",$A6))

When "none" is used on account restriction, then all sales that have no customer selected, or the customer does not have a charge account, are included.

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"accid=none")

When "any" is used on account restriction, then all sales that have ANY customer with a charge account selected are included.

=Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"accid=any")

For a given pinboard, the value of "accid=none" + "accid=any" equals the pinboard value if you hadn't included any account restriction. eg

    Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"accid=any")
PLUS
    Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023",,,"accid=none")

EQUALS
    Pinboard("product.sales.count.period",20,"1-jan-2022","1-jan-2023")

Available on

  • product.sales.count.period
  • product.sales.revenue.period
  • product.sales.linecount.period
  • product.sales.revenue.notax.period
  • product.sales.cost.period
  • department.sales.count.period
  • department.sales.revenue.period
  • department.sales.linecount.period
  • department.sales.revenue.notax.period
  • sales.count.period
  • sales.rawrevenue.total
  • sales.rawrevenue.notax
  • sales.rawrevenue.tax1
  • sales.count.layby
  • sales.count.parked