Lag Function Giving me Values that Don't Exist?

nshively
nshively Member
edited May 2023 in Beast Mode

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:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

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

Answers

  • MichelleH
    MichelleH Coach

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

  • nshively
    nshively 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.

  • MichelleH
    MichelleH Coach
    Answer ✓

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

  • MichelleH
    MichelleH 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?

  • GrantSmith
    GrantSmith 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • nshively
    nshively Member
    edited May 2023

    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.

Categories