Question

A formula to see if previous row has changed

  • 18 December 2018
  • 1 reply
  • 649 views

I am trying to find the % change in every instance there has been a change to the List Margin on each unique ID.


My data looks somewhat like this


ID List Margin

1234 1200

1234 1200

1234 1250

1234 1250

1235 1500

1235 1500

1235 1575

1236 1600

1236 1600

1236 1600

1236 1650


I need a formula for the following:


If the IDs are the same, then is there a difference in the List Margin from the row above (should yield YesNo). If there is a difference, then to calculate the % difference of the List Margin from that row to the row above.


1 reply

Userlevel 7
Badge +1

You could do this with a table calc!


Try something like


if(
${ID_LIST} = offset(${ID_LIST},-1) and ${margin} != offset(${margin},-1),
${margin}/offset${margin},-1),
null

Basically, that says: if the value in ID list is equal to the value offset by -1 from that (aka the row above it), AND the value in margin is different than the one offset by -1 from it, then perform the calculation, otherwise return null (or whatever you’d like to return).


If you need to do it in SQL instead, you’d have to use a window function PARTITIONed by ID_LIST 🙂 but that’d be specific to your flavor of sql. Give the table calc a whirl!

Reply