CPI logic for dummies in Coda
How to avoid common pitfalls?
In many countries, wages are adjusted annually to reflect changes in the cost of living. This mechanism, often called salary indexation, aims to protect workers from the eroding effects of inflation. A key component of this system is the Consumer Price Index (CPI), a measure of how the average price of a basket of goods and services purchased by households changes over time. The CPI acts as a barometer, signaling when wages need to be increased to maintain purchasing power. While increases are common in inflationary times, the political will to decrease wages during periods of deflation is often far weaker, creating an interesting asymmetry in the system. This post will explore the details of CPI calculation and highlight some common pitfalls to avoid.
Consider this example: a spreadsheet designed to track the consumer price index over several years. It includes columns for the effective date, year, a preceding value, and a new value.
The index calculation in this spreadsheet involves dividing the ‘new value’ by the ‘start value,’ a method that can be implemented using a function like Quotient. However, this approach, while seemingly straightforward, is problematic for spreadsheet management. It necessitates duplicating values, the new value of the preceding year equals the start value of the new year. In this post, we'll focus on how to avoid these data duplication pitfalls and create a more robust CPI tracking system.
The solution
To simplify the salary data, we can use a single column for all salary values instead of two. This column will hold all the necessary information. I’ve also added a row for 2008, which was implied but not explicitly included before.
We can then link this salary column to a date column, typically containing the first of January for each year. This date-based relationship allows us to filter the data and extract two key values: the “previous year’s salary” and the “current year’s salary.”
Let me show you how to find the previous year’s salary. We’ll use a filter and sorting to ensure the numbers are in the correct order, regardless of how the table is currently sorted. Just like the filter, the sorting is essential for this process. We’ll sort first by “True” and then by the effective date.
To find the “previous year’s salary,” we look for dates earlier than the date in the current row. Since we’re looking for earlier dates, this search will give us a list of all prior rows. The “previous year’s salary” is the salary value in the last row of this list. For example, in the third row (representing 2009), the “previous year’s salary” is the value from the 2008 row.
If there are no earlier rows (like for 2008), the “previous year’s salary” defaults to the salary in the current row.
With both these values, we can then perform the necessary calculations.
As demonstrated, a single column for salary values, combined with a filter, is sufficient to calculate the difference between salaries. This approach highlights the importance of data architecture, sorting and filtering techniques.
Understanding how tables function in this way can eliminate the need for spreadsheets for these types of calculations, as spreadsheets can be prone to errors when duplicating data.
Rolling total
To calculate the running total of annual salary increases, we can take the current year’s salary and add it to the previous year’s salary (which we multiply by -1 to make it negative). This gives us the difference between the current and previous year’s salaries.
To accommodate situations where the data might not be sorted by date, we can incorporate the filtering solution we discussed earlier to ensure accuracy regardless of the table’s order.
Calculating the running total from the calculated salary difference is then a straightforward process. We multiply the preceding value by -1 to make it negative, allowing us to sum it with the actual value.
Working with controllers
When working with controllers, you might want to calculate the running total between two specific years or express it as a percentage. In our example, this means defining a start year and an end year, with the calculation happening automatically. To simplify this, we can create a table of years and use a relation as a controller. I prefer this method over select items (though those could also be used). The process is as simple as shown below.
Our next steps involve setting up the controllers and ensuring the filters in the relationships are correctly configured (e.g., the end year is greater than the start year). A good starting point is to label the controllers with descriptive names.
I’ve broken down the calculation into several steps to make it easier to follow.
The crucial first step is to identify the time periods defined by the start and end years selected through the controllers. This process generates a list of periods, each associated with various numerical values, such as an index, the salary difference (delta), and the running total.
Step two showcases the elegance and power of the Coda Formula Language. We link the objects to the index named “single column logic” and then use the Product() function to multiply all the associated numbers.
The following step (which I’ll skip for brevity) involves subtracting 1 to express the result as a percentage. Finally, using the logic from step one and the first and last values from that list, we can perform a simple calculation, as shown below.
This canvas logic is relatively straightforward when the table is structured as shown. This approach reduces the likelihood of errors and allows calculations to run quickly due to the filtering logic.
Using this logic makes it quick and easy to index your prices and gain insights that might have been more difficult to obtain previously.
Hopefully, this post has shed some light on this common Coda challenge and empowered you to create more interactive and insightful data experiences. On a personal note, creating these in-depth posts takes a lot of time and effort. While I love sharing my knowledge, a little support goes a long way. If you found this helpful, what about a donation or sharing this post with your fellow Coda enthusiasts? Every bit of encouragement helps!
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
