Below is my dataset:
Date | Age | Amount |
---|---|---|
2023-01-01 | 20 | 1000 |
2023-02-01 | 25 | 2000 |
2023-03-01 | 23 | 2500 |
2023-04-01 | 40 | 5000 |
2023-05-01 | 30 | 3500 |
I want to create a toggle button which will group by Amount based on either Age or Date. But my sql takes both options and charts through error. Please help me how to fix this.
Below is the code that i have tried:
for dashboard file
- name: Group By
title: Group By
type: field_filter
default_value: ''
allow_multiple_values: false
required: true
ui_config:
type: button_toggles
display: inline
options: [Age,Date]
model: amount_summary
explore: amount_summary_analysis
listens_to_filters: []
field: amount_summary_analysis.Group_By
my sql code (view file code):
SELECT {% if Group_By._parameter_value == 'Age' %}
Age,
{% elsif Group_By._parameter_value == 'Date' %}
Date,
{%endif%}
COUNT(Amount) AS count_of_people
FROM amount_table
GROUP BY
{% if Group_By._parameter_value == 'Age' %}
Age,
{% elsif Group_By._parameter_value == 'Date' %}
Date,
{%endif%}
parameter: Group_By {
type: unquoted
allowed_value: { value: "Date"}
allowed_value: { value: "Age" }
}
dimension: Age {
type: Number
sql: ${TABLE}.Age ;;
}
dimension: Date {
type: date
sql: ${TABLE}.Date ;;
}
dimension: count_of_people {
type: number
sql: ${TABLE}.count_of_people ;;
}
measure: percentage_amt {
label: "Percentage Amt"
type: number
sql: {% if Group_By._parameter_value == 'Date' %}
ROUND(100*IFNULL(${Amount}/NULLIF(${count_of_people},0),0), 2)
{% endif %};;
required_fields: [Date]
measure: percentage_amt_age {
label: "Percentage Amt"
type: number
sql: {% if Group_By._parameter_value == 'Age' %}
ROUND(100*IFNULL(${Amount}/NULLIF(${count_of_people},0),0), 2)
{% endif %};;
required_fields: [Age]
Above throughs error, even after choosing one field it chooses both and throws error in explore, Please suggest me something how can i resolve it.