Using Parameters to Adjust Variables in a Looker Explore

Using Parameters to Adjust Variables in a Looker Explore

·

3 min read

I have a love/hate relationship with table calculations on Looker.

As an end-user, I understand they're easy to create quick insights without messing around with the LookML code (and some things can only be done with table calculations).

I like having things more permanent, not having to build them every time I accidentally close my tab or lose the explore I was working with. So that's the reason whenever I can; I'll try to translate it to LookML as soon as possible.

The Problem

Data could be better, so sometimes we need to make certain adjustments on the fly, but obviously, we don't want to adjust the whole database for a single use case.

I've seen these cases for financial adjustments (adding a percentage or bonus) and making some What-If statements, which can be interesting to adjust on multiple measures or different tiles on a dashboard.

The Solution

I've seen some Looker Devs trying to avoid liquid on Looker (and I sometimes understand the reasoning for that). However, in this case, I believe they're a good thing because they can give you a little bit of control over how we want to adjust the value without having to use table calculations or edit the DB or LookML code:

parameter: adjustment {
  type: number
}

parameter: adjustment_type {
  type: string
  allowed_value: {
    label: "Multiply"
    value: "multiply"
  }
  allowed_value: {
    label: "Divide"
    value: "divide"
  }
  allowed_value: {
    label: "Sum / Subtract"
    value: "sum"
  }
}

measure: adjusted_value {
  type: number
  sql: CASE
    WHEN {% parameter adjustment_type %} = "multiply"
      THEN ${value} * {% parameter adjustment %}
    WHEN {% parameter adjustment_type %} = "divide"
      THEN ${value} / {% parameter adjustment %}
    WHEN {% parameter adjustment_type %} = "sum"
      THEN ${value} + {% parameter adjustment %}
    ElSE
      ${value}
    END ;;
}

As you can see, we have two parameters: one defines the value, while the other selects the operator. They are a couple of variations for different use cases:

  • We can use the actual measure instead of creating a new one to avoid having it as a point of comparison.

  • We can eliminate the adjustment_type parameter if we know we want only one type of operation; this can also eliminate the whole CASE statement.

The Result

As you're going to be able to see in the image below, using this on your explore can give you that "User Input" that you're missing on Looker, adjusting the numbers on the fly:

Our only problem here is that we cannot use anything other than "is equal to" on the parameter, but you win and lose some.

Please let me know if you find an easier or more intuitive way to do this, and I will be forever grateful!