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
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.
You could do this with a table calc!
Try something like
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?
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)