# Lag Function Giving me Values that Don't Exist?

Member
edited May 2023

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.

Tagged:

• Coach

@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`)`

• Coach

@nshively Can you please share some examples of what values are returning vs what is expected?

• Member
edited May 2023

Here's an example of the table:

Effective Date

Job Title

Annual Base Salary 50th

1/15/2023

Writer, Senior

123611

10/15/2022

Writer, Senior

119674

4/15/2022

Writer, Senior

118442

7/15/2022

Writer, Senior

118032

1/15/2022

Writer, Senior

105409

What Domo is returning:

Job Title

Current Salary

Last Salary

Writer, Senior

123,611

144,980

The 'current salary' is returning the correct data, but the 'Last Salary' is higher than anything that has ever been on the table. I would expect it to return 119674.

• Coach

@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`)`

• Member

Okay, I tried this, but now it's returning an empty value (the formula is not giving me an error).

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

• Coach

@nshively Depending on the order of your data it may actually be pulling the next date instead of the previous. Does is return the correct value if you add ASC or DESC after your order by clause?

• Coach

@nshively - Is this the first record for the effective date for the job title? If so, lag can't get anything before it so it will return a null.

Also - just to confirm is your beast mode using back ticks (`) instead of single quotes as displayed in your formula above? I'm assuming so and it's just a formatting issue with the forums but wanted to confirm.

Another option that I prefer to do is utilizing a date dimension and having custom periods for PoP% calculations. I've done a write up on this in the past which you can find here: A more flexible way to do Period over Period comparisons