Restrictions

Excel pinboards provide precise key metrics, such as total sales revenue yesterday. Often though you want to filter or restrict the data selected even further. Owing to how excel works it isnt possible to expand the number of arguments to each pinboard infinitely, so instead many pinboards allow you to add restrictions (typically in the 6th argument).

Restrictions are keyword based, and not all restrictions apply to all pinboards.

From a performance perspective, using restrictions may be faster or slower than without restrictions. The effect depends on the backend database engine, the exact restriction, and whether the resulting database query is more optimised or not for the database.

Explanation

Consider a list of sales

Sale#	Date	Location	Customer Id	Teller Id	Weather
1001	1-Aug-2023 13:43	2	123	44	Sunny
1002	1-Aug-2023 13:48	2		44	Sunny
1003	1-Aug-2023 14:12	2		44	Sunny
1004	1-Aug-2023 16:32	2		44	Sunny
1005	1-Aug-2023 19:07	13	45687	27	Sunny
1006	2-Aug-2023 9:12	2	123	44	Raining
1007	2-Aug-2023 9:15	2	81	44	Raining

The pinboard

sales.count.period

returns the number of sales between two dates for a store location. So if we request this for both days, we get the answer of 7

=pinboard("sales.count.period", "1-aug-2023", "3-aug-2023")

However, if we want to restrict this to "cash sales" (those that don't have a known customer), then we add a restriction at the end

=pinboard("sales.count.period", "1-aug-2023", "3-aug-2023",,,,"cid=any")

And now the result is 4, being only those sales with a Customer Id

Multiple restrictions can be used. Here we request how many sales were made to customer 123 while the weather was raining ( result = 1 )

=pinboard("sales.count.period", "1-aug-2023", "3-aug-2023",,,,"cid=123 weather=raining")