Count changes in matching row data

1

I have a complex calculation I’m trying to do. I have a table of employment history for many people. Columns include:

  • person’s name
  • date started
  • employer
  • salary rate
    A person may appear multiple times in the table if they’ve gone through multiple job positions.

What I’m trying to figure out is, how many people saw an increase in their pay with a newer job. Is there a way to do this sort of comparison effectively? I know how to manually do this in Excel, using various sorting and formulas. But I’d like to automatically filter and count those instances.

2

Hi @Dimitri_Zakharov ,
would it be possible for you to share your doc or a sample of it to actually see the data structure?

That would help to understand how to find a viable solution.

Thank you!

3

I created a sample: Employment Tracker

Hope you can view this.

In this example, John and Allen both got new jobs that pay higher. So I’d like to 3 things:

  1. I’d like a Count number, YTD, of how many got a new job that pays higher than an old job. (i.e. 2)
  2. I’d like to filter/group to show those people. (create a view that just show John and Allen’s rows)
  3. I’d like to calculate the average increase. (i.e. $2.00)
4

I worked with Coda support and we came up with this solution: https://coda.io/d/Employment-Tracker-Calc_d8Mvy7jYadx