Question

How to create dynamic tiers for table calculation

  • 20 May 2019
  • 4 replies
  • 677 views

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.


4 replies

Userlevel 7
Badge +1

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.

Userlevel 7
Badge +1

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

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

Reply