How to create dynamic tiers for table calculation

I have joined two tables in explore and found the difference and rank between two date values using table calculations. Please can someone help me to create a dynamic tier for the column : “delay(in Minutes)” as attached in the image.

What do you mean by dynamic tier? Like creating a sort of bucket? You could grab the max() value of the column and the min() and do some comparisons based on that, perhaps.

Some more info would help answer the question in more detail.

I think he does mean buckets.

At the moment I believe it will only be able to be done using a table calculation with a lot of IF statements.

That is if you can’t achieve the grouping mathematically, for example 0-10.99 then 11-120 anything like that will required calculations

1 Like

For example: I want the frequency of the count that occurs between 0-5 or 0-10.
If that is not possible, at least frequency of count of the occurrence. For example: How many times 1 is occuring?

A dynamic tiers sample for our project.

in model

explore: custom_functions {
    extension: required
    sql_preamble:
       CREATE TEMP FUNCTION _gt_tier( value FLOAT64, config STRING)
          RETURNS STRING
          AS(
            CASE
            WHEN config='' THEN CAST(value AS STRING)
            WHEN value IS NULL THEN NULL
            WHEN SPLIT(config, ',')[SAFE_OFFSET(0)] ='x' THEN NULL
            WHEN SPLIT(config, ',')[SAFE_OFFSET(0)] ='n' THEN
              CASE
                WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) THEN
                   CONCAT("<",SPLIT(config, ',')[SAFE_OFFSET(3)] )
                WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) IS NOT NULL AND value >= SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) THEN
                   CONCAT(">=",SPLIT(config, ',')[SAFE_OFFSET(4)] )
                ELSE
                  CONCAT(CAST(FLOOR(SAFE_DIVIDE((value-IFNULL(SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64),0)), SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64))  ) * SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64)+IFNULL(SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64),0) AS STRING)
                  ,"-",  CAST((FLOOR(SAFE_DIVIDE((value-IFNULL(SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64),0)), SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64))  ) +1)* SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64)+IFNULL(SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64),0) AS STRING))
                END
            WHEN SPLIT(config, ',')[SAFE_OFFSET(0)] ='m' THEN
              CASE WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64) THEN
                   CONCAT("<",SPLIT(config, ',')[SAFE_OFFSET(1)] )
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(1)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(2)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(2)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(3)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(3)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(4)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(5)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(5)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(4)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(5)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(6)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(6)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(5)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(6)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(7)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(7)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(6)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(7)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(8)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(8)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(7)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(8)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(9)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(9)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(8)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(9)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(10)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(10)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(9)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(10)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(11)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(11)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(10)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(11)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(12)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(12)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(11)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(12)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(13)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(13)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(12)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(13)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(14)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(14)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(13)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(14)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(15)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(15)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(14)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(15)])
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(16)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(16)] AS FLOAT64) THEN
                   CONCAT(SPLIT(config, ',')[SAFE_OFFSET(15)] ,"-", SPLIT(config, ',')[SAFE_OFFSET(16)])
              ELSE
                   CONCAT(">=",SPLIT(config, ',')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(config, ','))-1)] )
              END
            ELSE  CAST(value AS STRING) END
          );
          CREATE TEMP FUNCTION _gt_tier_sort( value FLOAT64, config STRING)
          RETURNS INT64
          AS(
            CASE
            WHEN config='' THEN SAFE_CAST(value AS INT64)
            WHEN value IS NULL THEN NULL
            WHEN SPLIT(config, ',')[SAFE_OFFSET(0)] ='x' THEN NULL
            WHEN SPLIT(config, ',')[SAFE_OFFSET(0)] ='n' THEN
              CASE
                WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS INT64)-1
                WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) IS NOT NULL AND value >= SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS INT64)+1
                ELSE
                  CAST(FLOOR(SAFE_DIVIDE((value-IFNULL(SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64),0) ), SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64) ))* SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64)+IFNULL(SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64),0) AS INT64)
                END
            WHEN SPLIT(config, ',')[SAFE_OFFSET(0)] ='m' THEN
              CASE
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(1)] AS INT64)-1
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(2)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(3)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(4)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(5)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(5)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(5)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(6)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(6)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(6)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(7)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(7)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(7)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(8)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(8)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(8)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(9)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(9)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(9)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(10)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(10)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(10)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(11)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(11)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(11)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(12)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(12)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(12)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(13)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(13)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(13)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(14)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(14)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(14)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(15)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(15)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(15)] AS INT64)
              WHEN SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(16)] AS FLOAT64) IS NOT NULL AND value < SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(16)] AS FLOAT64) THEN
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(16)] AS INT64)
              ELSE
                   SAFE_CAST(SPLIT(config, ',')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(config, ','))-1)] AS INT64 )+1
              END
            ELSE SAFE_CAST(value AS Int64) END
          );
        ;;
    }
    

explore: a {
  extends: [custom_functions]
  #....
  }
  
explore: b {
  extends: [custom_functions]
  #....
  }

in views

view: a{ 
  #....

  parameter: minute_tier_config  {
    type: string
    default_value: ""
    suggestable: yes
    suggestions: ["x","n,10,0","n,5,0","m,5,10,20,40"]
  }

  dimension: minute_tier{
    type:string
    sql:_gt_tier(${minute},{% parameter minute_tier_config%});;
    order_by_field: minute_tier_sort
  }

  dimension: minute_tier_sort {
    type: number
    hidden: yes
    sql:_gt_tier_sort(${minute},{% parameter minute_tier_config%}) ;;
    }

}
view: b{
#....

  parameter: minute_tier_config  {
    type: string
    default_value: ""
    suggestable: yes
    suggestions: ["x","n,10,0","n,5,0","m,5,10,20,40"]
  }

  dimension: minute_tier{
    type:string
    sql:_gt_tier(${minute},{% parameter minute_tier_config%});;
    order_by_field: minute_tier_sort
  }

  dimension: minute_tier_sort {
    type: number
    hidden: yes
    sql:_gt_tier_sort(${minute},{% parameter minute_tier_config%}) ;;
    }
 
} 

use minute_tier_config to set tier

minute_tier_config= x or “” (keep origin value)
minutes_tier
1.20
12.30
23.40
43.34
34.34
343.34

minute_tier_config= “n,10,0,0,100” (n, size, offset, min, max )
minutes_tier
<0
0-10
10-20
20-30

>=100

minute_tier_config= “n,10,1,1,100” (n, size, offset, min, max )
minutes_tier
<1
1-11
11-21
21-31

>=100

minute_tier_config= “m,10,30,50,100” (m, …)
minutes_tier
<10
10-30
30-50
50-100
>=100

2 Likes