Creating new column based on another column with flexibility

  • 28 February 2019
  • 1 reply

Hi I am new to Looker. I already created a table with some aggregation in explore as below:

as you can see, for each state (column), I aggregated median value by model_month (200101-201601). Then I generated another column called ‘INDEX’ based on ‘Median_Value’ column (for each state).

here is the code I wrote to create ‘INDEX’:

round((${median_value} / offset(${median_value}, 1-row()) ) * 100, 2)

basically this means I use first row of Median_Value (which is when model_month is 200101) as anchor (INDEX = 100) then calculate the following index as:

(median_value/median_value at 200101) * 100

In this way I can only create INDEX column based on first row. What if I want to use median_value at 200601 (for example) as anchor (index =100) ? I want to give the client the flexibility to choose which model_month they want to set as anchor (index = 100) from dashboard.

Is that achievable in Looker?

Thank you very much !!!

1 reply

Userlevel 7
Badge +1

I think this is definitely achievable, but you’d have to do it in LookML as opposed to a table calculation— And, since you’re doing an offset function, you’d probably have to do a derived table in order to use a SQL LAG function.

Basically, eventually, you could use a parameter or templated filter to capture the users input as a filter on the dashboard, and dynamically insert that into the sql of the derived table— There’s a few examples on that doc! Give that a read and see if that approach makes sense.