Question

A formula to see if previous row has changed

  • 18 December 2018
  • 3 replies
  • 899 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.


3 replies

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!

Can we do the same thing in lookml?

Userlevel 7
Badge +1

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)

Reply