A formula to see if previous row has changed

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!

If you need to do it in LookML, you would have to do it in a derived table. The advantage of table calculations is that they work on data returned from SQL whereas LookML defines what SQL is sent to the database (apart from running_sum field).  The best way to test if you could do something is to write the SQL first and then see if that kind of SQL can be split into dimensions and measures, or whether it has to be put as a derived table (or better yet be part of your data model)