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

MichelleH

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

nshively

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.

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

nshively

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')

MichelleH

@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?

GrantSmith

@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

nshively

Thank you @MichelleH and @GrantSmith

The solution was a combination of adding the partition and realizing I had made a simple mistake. I forgot I had filtered by only the last effective date, so the partition was trying to pull a value that didn't exist. The partition was the piece I was missing, though.

The problem is that when I remove the date filter, I get a job title value for each effective date (1 for the current quarter and 1 for the previous quarter), however, I think I've solved this by filtering out any jobs that have a null value for their previous job salary. Unfortunately, this will not work if I want to use more than 2 time periods, but it will work with any 2 time periods as long I specifically filter by them.