Interactive Chart & Checkbox Group

App Building
1

Hey all!

Possible silly question but I was hoping you could help, I'm trying to build an interactive chart where based on what is checked in the checkbox group values are summed and then referenced by the chart.

This is the checkbox group I am using

Screenshot 2024-04-25 at 11.34.45
Screenshot 2024-04-25 at 11.34.45646×510 4.95 KB

and this is a snippet of the code:

case
  when {{checkboxGroup1.selectedIndexes[0,1,2] = true}} then sum(gmv_across_verticals) 
  when  {{checkboxGroup1.selectedIndexes[0,1] = true}} then (coalesce(sum(gmv_activities),0) + coalesce(sum(bb_gmv_confirmed),0))
  when  {{checkboxGroup1.selectedIndexes[0,2] = true}} then (coalesce(sum(nursery_gmv),0) + coalesce(sum(bb_gmv_confirmed),0))
  when  {{checkboxGroup1.selectedIndexes[0] = true }} then (sum(bb_gmv_confirmed))
  when  {{checkboxGroup1.selectedIndexes[1] = true}} then (sum(gmv_activities))
  when  {{checkboxGroup1.selectedIndexes[2] = true }} then (sum(nursery_gmv))
  when {{checkboxGroup1.selectedIndexes[1,2] = true }} then (coalesce(sum(gmv_activities),0) + coalesce(sum(nursery_gmv),0))
  else 0
end

It may be a very silly way of going about it but this is what I thought could work, the problem is that the query runs but it is un-affected by the selection in the checkbox group, any help would be appreciated!

2

I think you might have more success adding all of the possibilities as different fields in the output, and then directing your chart to use the appropriate one based on the checkbox values. That way, you run the SQL once and just move through the results in your chart bars on the checkboxes.

3

Thanks for the tip! I've attempted this syntax in the plotly.js code but I get an error that the value has to be type of object but I provided string:

[
  {
    "name": "Bookings",
    "x": {{marketplace.data['day']}},
    "y": "y":{{checkboxGroup1.selectedIndexes[0]=true? query6.data['bb_gmv_confirmed']:checkboxGroup1.selectedIndexes[1]=true? query6.data['act_bb']:checkboxGroup1.selectedIndexes[2]=true? query6.data['nur_bb']:checkboxGroup1.selectedIndexes[0,1,2]=true? query6.data['all_gmv']:checkboxGroup1.selectedIndexes[0,1]=true? query6.data['act_bb']:checkboxGroup1.selectedIndexes[0,2]=true? query6.data['nur_bb']:query6.data['act_nur']}},
    "type": "line",
    "hovertemplate": "<b>%{x}</b><br>%{fullData.name}: %{y}<extra></extra>",
    "transforms": [
      {
        "type": "groupby",
        "groups": {{marketplace.data['month_type']}},
        "styles": [
          {
            "target": {{_.uniq(marketplace.data['month_type'])[0]}},
            "value": {
              "marker": {
                "color": "#7EA8F1"
              }
            }
          },
          {
            "target": {{_.uniq(marketplace.data['month_type'])[1]}},
            "value": {
              "marker": {
                "color": "#0F216F"
              }
            }
          }
        ]
      },
      {
        "type": "sort",
        "target": {{marketplace.data['day']}},
        "order": "ascending"
      },
      {
        "type": "aggregate",
        "groups": {{marketplace.data['day']}},
        "aggregations": [
          {
            "target": "y",
            "func": "sum",
            "enabled": true
          }
        ]
      }
    ],
    "mode": "lines"
  }
]
4

You need to use == to check equality, and you have to check each index separately. Maybe try an if/else approach. Something like this should work:

if (checkboxGroup1.selectedIndexes[0] == true) {
    result = query6.data['bb_gmv_confirmed'];
} else if (checkboxGroup1.selectedIndexes[1] == true) {
    result = query6.data['act_bb'];
} else if (checkboxGroup1.selectedIndexes[2] == true) {
    result = query6.data['nur_bb'];
} else if (checkboxGroup1.selectedIndexes[0] || checkboxGroup1.selectedIndexes[1] || checkboxGroup1.selectedIndexes[2]) {
    result = query6.data['all_gmv'];
} else if (checkboxGroup1.selectedIndexes[0] && checkboxGroup1.selectedIndexes[1]) {
    result = query6.data['act_bb'];
} else if (checkboxGroup1.selectedIndexes[0] && checkboxGroup1.selectedIndexes[2]) {
    result = query6.data['nur_bb'];
} else {
    result = query6.data['act_nur'];
}