I am trying to create a measure conditionally based on a dimension.
column is a dimension while geo_area.count_anon and count_anon are measures of sum type.
This measure is not working well.
measure:measure_count_lvl3 {
type: number
sql:
CASE
WHEN ${column}='GEO_AREA_ID' THEN ${geo_area.count_anon}
ELSE ${count_anon}
END ;;
}
The error:
What can I do to solve this?
Hey Miguel!
The issue is coming as you are using a type number. Type number usually should be used to do a calculation.
In your case I suggest you to change the measures used in the CASE when statement with the dimensions that you’ve used in those measures. Like this:
measure: geo_area.count_anon {
type: sum
sql:${dimension_1} ;;
}
measure: count_anon {
type: sum
sql:${dimension_2} ;;
}
measure:measure_count_lvl3 {
type: sum
sql:
CASE
WHEN ${column}='GEO_AREA_ID' THEN ${dimension_1}
ELSE ${dimension_2}
END ;;
}
Hope that helps you!
${column} is not in your query / data table,hence your problem. I’m assuming you don’t want it but want just one measure.
You would actually need this logic in the sum measure:
measure: some_sum {
type: sum
sql: IF(${column} = "GEO_AREA_ID", ${count_column_a}, ${count_column_b}) ;;
}
What this would do, it will make sure that ${column} is within an aggregation so it will be evaluated on row level and there’s no more need for grouping.
Hey Miguel!
The issue is coming as you are using a type number. Type number usually should be used to do a calculation.
In your case I suggest you to change the measures used in the CASE when statement with the dimensions that you’ve used in those measures. Like this:
measure: geo_area.count_anon {
type: sum
sql:${dimension_1} ;;
}
measure: count_anon {
type: sum
sql:${dimension_2} ;;
}
measure:measure_count_lvl3 {
type: sum
sql:
CASE
WHEN ${column}='GEO_AREA_ID' THEN ${dimension_1}
ELSE ${dimension_2}
END ;;
}
Hope that helps you!
I’ve been trying that and finally it works puting type sum in measure_count_lvl3 but I have to put a type number in the dimension 1 and 2.
This problem appears but it seems to work anyway.
Hey Miguel!
The issue is coming as you are using a type number. Type number usually should be used to do a calculation.
In your case I suggest you to change the measures used in the CASE when statement with the dimensions that you’ve used in those measures. Like this:
measure: geo_area.count_anon {
type: sum
sql:${dimension_1} ;;
}
measure: count_anon {
type: sum
sql:${dimension_2} ;;
}
measure:measure_count_lvl3 {
type: sum
sql:
CASE
WHEN ${column}='GEO_AREA_ID' THEN ${dimension_1}
ELSE ${dimension_2}
END ;;
}
Hope that helps you!
Well, actually I’ve working on it and still giving me mistakes. How can I call a sth from another view? Am I explaining myself?
you need to reference the view name ${view_name.field}
you need to reference the view name ${view_name.field}
But now I have this error
Hey Miguel!
Can you share the definition of the other measures and dimensions that you have now?
Then I think you’re back to the original problem. You have a measure of type number with SUM and a dimension , you expect it to get aggregated but it won’t. Anythin with type:number, be it a dimension or measure, isn’t going to be treated as an aggregation: it doesn’t get wrapped in SUM(), COUNT() or anything like that
Hey Miguel!
Can you share the definition of the other measures and dimensions that you have now?
Yes sure
geo_area view:
measure: count_anon {
type: sum
sql: ${TABLE}.count_anon ;;
}
lvl3 view (Which is the view Im working on):
dimension: column {
type: string
sql: ${TABLE}.column ;;
}
measure: count_anon {
type: sum
sql: ${TABLE}.count_anon ;;
}
measure:measure_count_lvl3 {
type: sum
sql:
CASE
WHEN ${column}='GEO_AREA_ID' THEN ${geo_area.count_anon}
ELSE ${count_anon}
END ;;
}
I also tried the next measure in case the one before but I have the same mistake
measure:measure_count_lvl3_IF {
type: sum
sql: IF(${column}='GEO_AREA_ID',${geo_area.count_anon},${count_anon}) ;;
}
My objective is to show the geo_area.count_anon column when column is filter by GEO_AREA_ID or show the count_anon when it isnt
The definition of the dimensions and the measure should be like this:
dimension: dim_1{
type: number
}
dimension: dim_2{
type: number
}
measure:measure_count_lvl3 {
type: sum
sql:
CASE
WHEN ${column}='GEO_AREA_ID' THEN ${dim_1}
ELSE ${dim_2}
END ;;
}
You don’t need to create additional measures to build the measure measure_count_lvl3
Have a try doing it this way:
# Geo Area
dimension: count_anon_dim {
type: number
sql: ${TABLE}.count_anon ;;
}
# LVL3 View
dimension: count_anon_dim {
type: number
sql: ${TABLE}.count_anon ;;
}
measure: measure_count_lvl3 {
type: sum
sql: IF(${column} = 'GEO_AREA_ID', ${geo_area.count_anon_dim}, ${count_anon_dim}) ;;
}
Thank you,
Now there is no mistake but I shouldnt be 0, I dont know what is the problem now.
Anyways, thanks for everything