Skip to content
Gallery
Good habits: aggregate and optimize
Good habits: aggregate and optimize
Strategy one
Strategy two
Strategy three
Conclusion
More
Share
Explore

icon picker
Strategy three

Ben Lee

Re-use, not repeat

Approach

“If it’s repeatable, it’s programmable”
Find repetitive formulas, place what’s in common in one column, then reference that column for all others.
⁠
If we show our column formulas for the five data points we’re calculating in strategies one and two, we can see what they have in common.

Strategy one

⁠
image.png
⁠
⁠

Strategy two

⁠
image.png
⁠
⁠
This isn’t just quite a bit of repetition, it’s also the more expensive part of the formula to calculate. Since strategy two was already an improvement of 5x, let’s start there and see if we can continue down this path of optimization.
Our goal for this strategy is to get everything highlighted as repetitive in the red boxes above down to one “worker column” called Sales Orders with the following formula:
⁠
image.png
⁠
⁠
Look familiar?
The trick to this strategy isn’t just taking all repeated elements and only running them once, it’s that the formula we’re using returns row values instead of one piece of data from the column.
Coda made the decision early on to use
Tables not Spreadsheets
⁠
giving us this ability to use row values and all data within them. Thanks to our super-powered formula language, it only takes a . to extract any column specific values.
Since the tough work of the filter is already done and in the worker column Sales Orders, we simply reference that column, add a . and then type the column with the values we need.
⁠
Strategy three: country table =
185
⁠
⁠
Strategy three: sales report =
1000
⁠
⁠
1
Sales Orders
Calculations: 185,000
⁠
image.png
⁠
⁠
2
Count of Sales Orders per Country
Calculations: 1,000
image.png
3
Total Units per Country
Calculations: 1,000
image.png
4
Total Profit per Country
Calculations: 1,000
image.png
5
Total Cost per Country
Calculations: 1,000
image.png
6
Most Recent Ship Date per Country
Calculations: 1,000
image.png
There are no rows in this table
⁠

Total calculations for five data points: 189,000

This is over 25x more optimal than where we started with strategy one. And for additional data points, we actually have a 1,000x improvement. We’re not only more performant with calculations, we have much shorter and easier to understand formulas to work with as well as maintaining the gains from strategy two with easier to read aggregate data.

Wait, there’s more!

Since we returned row values with our Sales Orders column, and since row values contain the data for that row, Coda can display those values as subtables of the country table.
If we change the layout of the country table to a Detail view, we can then edit the layout and have those row chips displayed as an actual table.
Detail view with subtables
1
Search
Afghanistan
Albania
Algeria
Andorra
Angola
Antigua and Barbuda
Armenia
Australia
Austria
Azerbaijan
Bahrain
Bangladesh
Barbados
Belarus
Belgium
Belize
Benin
Bhutan
Bosnia and Herzegovina
Botswana
Brunei
Bulgaria
Burkina Faso
Burundi
Cambodia
Cameroon
Canada
Cape Verde
Central African Republic
Chad
China
Comoros
Costa Rica
Cote d'Ivoire
Croatia
Cuba
Cyprus
Czech Republic
Democratic Republic of the Congo
Denmark
Djibouti
Dominica
Dominican Republic
East Timor
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Ethiopia
Federated States of Micronesia
Fiji
Finland
France
Gabon
Georgia
Germany
Ghana
Greece
Greenland
Grenada
Guatemala
Guinea
Guinea-Bissau
Haiti
Honduras
Hungary
Iceland
India
Indonesia
Iran
Iraq
Ireland
Israel
Italy
Jamaica
Japan
Jordan
Kazakhstan
Kenya
Kiribati
Kosovo
Kuwait
Kyrgyzstan
Laos
Latvia
Lebanon
Lesotho
Liberia
Libya
Liechtenstein
Lithuania
Luxembourg
Macedonia
Madagascar
Malawi
Malaysia
Maldives
Mali
Malta
Marshall Islands
Mauritania
Mauritius
Mexico
Moldova
Monaco
Mongolia
Montenegro
Morocco
Mozambique
Myanmar
Namibia
Nauru
Nepal
Netherlands
New Zealand
Nicaragua
Niger
Nigeria
North Korea
Norway
Oman
Pakistan
Palau
Panama
Papua New Guinea
Philippines
Poland
Portugal
Qatar
Republic of the Congo
Romania
Russia
Rwanda
Saint Kitts and Nevis
Saint Lucia
Saint Vincent and the Grenadines
Samoa
San Marino
Sao Tome and Principe
Saudi Arabia
Senegal
Serbia
Seychelles
Sierra Leone
Singapore
Slovakia
Slovenia
Solomon Islands
Somalia
South Africa
South Korea
South Sudan
Spain
Sri Lanka
Sudan
Swaziland
Sweden
Switzerland
Syria
Taiwan
Tajikistan
Tanzania
Thailand
The Bahamas
The Gambia
Togo
Tonga
Trinidad and Tobago
Tunisia
Turkey
Turkmenistan
Tuvalu
Uganda
Ukraine
United Arab Emirates
United Kingdom
United States of America
Uzbekistan
Vanuatu
Vatican City
Vietnam
Yemen
Zambia
Zimbabwe
Afghanistan
Most Recent Ship Date per Country
1/16/2017
Count of Sales Orders per Country
6
Total Units Sold per Country
22651
Total Profit per Country
$1,365,407.10
Total Cost per Country
$1,478,181.97
Sales Orders
Search
1
2
3
4
5
6
Order ID
Order Date
Item Type
Units Sold
Total Profit
Unit Cost
Sales Channel
551136291
1/13/2017
Clothes
2331
$171,188.64
$35.84
Offline
446991050
12/5/2016
Clothes
3440
$252,633.60
$35.84
Online
410067975
10/13/2016
Cereal
7081
$627,305.79
$117.11
Online
808538234
12/19/2015
Cereal
3286
$291,106.74
$117.11
Offline
967644727
4/22/2013
Fruits
6433
$15,503.53
$6.92
Online
767401731
7/8/2012
Baby Food
80
$7,668.80
$159.42
Online
There are no rows in this table
⁠

⁠
Conclusion
⁠
Strategy three: country table
1
⁠
Strategy three: sales report
⁠


Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.