My aggregation functions are not working!
I am expecting calculation results AVG, SUM, MAX in measures, but getting only field value
Can anyone identify what I'm doing wrong?
explore: test2 {
symmetric_aggregates: yes
}
view: test2 {
derived_table: {
sql:
WITH EXAMPLE_TABLE AS (
SELECT 1 as ID, 1 as AGE, "Peter" as name, "M" as gender
UNION ALL
SELECT 2 as ID, 2 as AGE, "Oleg" as name, "M" as gender
UNION ALL
SELECT 3 as ID, 4 as AGE, "Ian" as name, "M" as gender
UNION ALL
SELECT 4 as ID, 15 as AGE, "Olga" as name, "F" as gender
UNION ALL
SELECT 5 as ID, 16 as AGE, "Diana" as name, "F" as gender
UNION ALL
SELECT 6 as ID, 50 as AGE, "Janet" as name, "F" as gender
)
SELECT * FROM EXAMPLE_TABLE ;;
}
dimension: name {
description: "Name"
sql: ${TABLE}.name ;;
}
dimension: AGE {
description: "AGE"
type: number
sql: ${TABLE}.age ;;
html: <b>{{ value }}</b> ;;
}
dimension: gender {
description: "Gender"
type: number
sql: ${TABLE}.gender ;;
html: <b>{{ value }}</b> ;;
}
measure: avg_age1 {
description: "AVG_AGE1"
# type: sum
type: average
hidden: no
sql: age ;;
}
measure: AVG_AGE2 {
description: "AVG_AGE2"
sql: AVG(${AGE}) ;;
# value_format_name: usd
}
measure: MAX_AGE {
description: "MAX_AGE"
type:sum
sql: ${TABLE}.age ;;
}
}
The result is just copy of value from field
But I expect 50 in all rows in column MAX_AGE
For example
measure: MAX_AGE {
description: "MAX_AGE"
type:sum
sql: (select max(age) from ${TABLE}) ;;
}
Does the work, but problematic for filtering