Lag Function Giving me Values that Don't Exist?

nshively

I'm trying to create what should be a simple table that shows job Last Quarter Salary, This Quarter Salary, and Percent Change. However, my Beast Mode is giving me values that aren't even on my table.

The Current Salary is displaying fine, but the previous quarter's salary is giving me a fictional number.

Here's my formula to pull the previous salary:

lag((Annual Base Salary 50th),1) over (order by Data Effective Date)

Then my change calculation would be:

Annual Base Salary 50th -(lag((Annual Base Salary 50th),1) over (order by Data Effective Date)) / (lag((Annual Base Salary 50th),1) over (order by Data Effective Date))

Effective date is broken down by quarters but uses a specific day (2023-01-15, for example).

I filter the data to the current quarter, so it isn't trying to display the same job for multiple quarters. According to Domo, the formula is valid but the numbers are wrong.

MichelleH

@nshively Try adding a partition to your window function so that it only looks at the last salary for the same job title:

lag((`Annual Base Salary 50th`),1) over (partition by `Job Title` order by `Data Effective Date`)