Using LAG() OVER function to get difference in a Snapshot dataset

Options
brandenmjenkins
brandenmjenkins Member
edited March 31 in Beast Mode

Hello!

I am trying to get the right formula to calculate the difference in data within a table (same data APPENDED to create snapshots).

image.png

Here is an example of a beast mode I have been playing with. I have unique data by Opportunity ID & Snapshot YearWeek in the rows summarized in this chart, I would like to take the 202413 YearWeek vs. 202412 YearWeek's Pipeline Open to calc the change +/-. And so on for each Snapshot YearWeek

Pipeline Open - LAG(Pipeline Open, 1, 0) OVER (PARTITION BY Opportunity ID ORDER BY Snapshot YearWeek DESC)

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    You're close, you'd need something like:

    `Pipeline Open` - LAG(`Pipeline Open`) OVER (ORDER BY `Snapshot YearWeek` DESC)

    LAG only takes 1 or 2 arguments, the field name and the number of rows back (optional - defaults to 1)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    You're close, you'd need something like:

    `Pipeline Open` - LAG(`Pipeline Open`) OVER (ORDER BY `Snapshot YearWeek` DESC)

    LAG only takes 1 or 2 arguments, the field name and the number of rows back (optional - defaults to 1)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories