How to create a chart based on toggle button in lookml?

22 Views Asked by At

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.

0

There are 0 best solutions below