A formula to see if previous row has changed


(Naseha Khan) #1

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.


(Izzy) #2

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 :slight_smile: but that’d be specific to your flavor of sql. Give the table calc a whirl!