How to Create dynamic tiers in LookML?

To create dynamic tiers in LookML, you can use Looker’s advanced modeling language to define a measure with tiered thresholds. The tier thresholds can be calculated based on a measure’s values, or you can manually define them.

Create a parameter of type: number to serve as the frontend filter field where the user can enter the numerical bucket size they would like.
Create a dimension that references the parameter value with the Liquid variable {% parameter parameter_name %}. This dimension determines the various buckets and will dynamically change the bucket size to the value entered by the user in the frontend filter field (the parameter parameter).

For example, a developer creates a dynamic age tier that lets users bucket age values by custom ranges:

The SQL syntax for the following example may need to be adapted to suit your database dialect.

  parameter: age_tier_bucket_size {     type: number   }    dimension: dynamic_age_tier {     type: number     sql: TRUNCATE(${TABLE}.age / {% parameter age_tier_bucket_size %}, 0)           * {% parameter age_tier_bucket_size %} ;;   }

A user can now choose tier values for the Age column in an Explore. For example, a user might want to see ages grouped into 10-year buckets and so enter the value 10 in the Age Tier Bucket Size filter:

The SQL expression in the dynamic_age_tier dimension divides an age value from the underlying ${TABLE}.age column — for example, 25 — by the parameter value of 10, resulting in 2.5. The value 2.5 is truncated to 2 by the TRUNCATE function and is multiplied by the parameter value 10, resulting in 20. 20 becomes the bucket; any age value between 20 and 29 is included in the 20 buckets.

Here’s an example of creating a dynamic tier based on the revenue measure:

Define a measure for revenue in your LookML model:

  measure: revenue {
    type: sum
    sql: ${TABLE}.revenue ;;
  }

2. Define a measure for revenue in your LookML model:

  dimension: revenue_tier {
    type: tier
    sql: ${revenue} ;;
    tiers: [
      {value: 1000, label: "Low Revenue"},
      {value: 5000, label: "Medium Revenue"},
      {value: 10000, label: "High Revenue"}
    ]
  }

In this example, we’re using the “tier” dimension type to create a dynamic tier based on the revenue measure. The sql parameter references the revenue measure, and the tiers parameter defines the threshold values and labels for each tier.

Use the dynamic tier in your LookML views:

  view: sales {
    dimension: revenue_tier {
      group_label: "Revenue Tier"
    }
  }

In this example, we’re adding the revenue_tier dimension to the sales view and setting the group_label parameter to “Revenue Tier”.

With this configuration, the revenue_tier dimension will automatically categorize the revenue data into Low, Medium, or High tiers based on the threshold values defined in the tiers parameter. The tier values and labels can be easily modified, and the dynamic tier can be used in other views or explores as needed.

_________________________________________________________________________________________

More Imp Topics

How Looker Writes SQL?

How to calculate percent of total in looker?

Table Calculations in Looker

What are Derived tables in Looker?

How to set conditional formatting in LOOKER?

Looker Interview Questions And Answers

New Looker Performance Recommendations Dashboard

Looker Git Version Control: How To Revert To A Specific Commit In Looker, No Git Commands Necessary

________________________________________________________________________________________

Reference :

About Me:-
I am Om Prakash Singh – Data Analytics Consultant, Looker Consultant, and Solution Architect.
I am a Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation, and visualization. Furnish insights, analytics, and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us if you are interested to evaluate if Looker is right for you or any other BI solution.

How to Create dynamic tiers in LookML?