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.
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
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?
Userlevel 7
+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
Userlevel 7
+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.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.