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

brandenmjenkins

Hello!

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

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)

GrantSmith

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)

GrantSmith

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)