Calculating percentages inline for texttemplate plotly.js data JSON attribute

App Building
, ,
1

Hello! I have many bar charts displaying survey results as counts, and I would like to add data labels to each bar that show the values as percentages. I'm using horizontal bar charts, so the count is on x and the categories are y.
It seems ALMOST possible to calculate the percentage for each {x}. I can display "/*100", as seen in the attached image, but cannot get the mathematical function to actually run using any combination of parentheses and curly braces. I have also tried triggering a query to calculate the percentage using additionalScope to pass {x}, but this seemed less promising.
Is it even possible to calculate this percentage inline in this context? If not, any other suggestions for programmatically getting percentage labels on these charts?

Plotly.js JSON data:

[
  {
    "name": "Count of Participants",
    "x": {{formatDataAsObject(addNullsToEmptyResponses.value)}},
    "y": {{formatDataAsObject(addNullsToEmptyResponses.value).health_last4w_overall}},
    "type": "bar",
    "orientation": "h",
    "texttemplate": "%{x}/{{addNullsToEmptyResponses.value.length}}*100",
    "textposition": "outside",
    "transforms": [
      {
        "type": "sort",
        "target": {{formatDataAsObject(addNullsToEmptyResponses.value).health_last4w_overall}},
        "order": "ascending"
      },
      {
        "type": "aggregate",
        "groups": {{formatDataAsObject(addNullsToEmptyResponses.value).health_last4w_overall}},
        "aggregations": [
          {
            "target": "x",
            "func": "count",
            "enabled": true
          }
        ]
      }
    ],
    "marker": {
      "color": "#033663"
    }
  }
]

Plotly.js JSON format:

{
  "title": {
    "text": "",
    "font": {
      "color": "#3D3D3D",
      "size": 16
    }
  },
  "font": {
    "family": "Inter",
    "color": "#979797"
  },
  "showlegend": false,
  "legend": {
    "xanchor": "center",
    "x": 0.5,
    "y": 1.1,
    "orientation": "h"
  },
  "margin": {
    "l": 72,
    "r": 24,
    "t": 24,
    "b": 72,
    "pad": 2
  },
  "hovermode": "closest",
  "hoverlabel": {
    "bgcolor": "#000",
    "bordercolor": "#000",
    "font": {
      "color": "#fff",
      "family": "Inter",
      "size": 12
    }
  },
  "clickmode": "select+event",
  "dragmode": "select",
  "xaxis": {
    "title": {
      "text": "Participant Count",
      "standoff": 6,
      "font": {
        "size": 12
      }
    },
    "type": "-",
    "tickformat": "",
    "automargin": true,
    "fixedrange": true,
    "gridcolor": "#fff",
    "zerolinecolor": "#fff"
  },
  "yaxis": {
    //"type": "linear",
    "tickformat": "",
    "automargin": true,
    "fixedrange": true,
    "zerolinecolor": "#DEDEDE",
    "categoryarray": ["very-poor", "poor", "fair", "good", "very-good", "excellent"],
    "categoryorder": "array",
  },
  "barmode": "stack"
}

Screenshot 2023-02-14 at 1.24.20 PM

2

Plotly can be a tricky beast to master, I can empathise!

The texttemplate property you're using is a substitution template using D3 notation - so what Plotly is doing is treating it as a string and only swapping out {x} with the value you supply to it and ignoring the rest.
What I do is just set the text property instead of the template and use double curly brackets as usual.

ie something like this

"text": {{chart_data.value.score*100+'%'}},
3

Thanks! Using text instead of texttemplate seems promising, but I'm not sure how to grab the values for each bar in the chart. Since the chart clears when syntax errors are present, I also can't experiment much with grabbing the correct values from the chart object. Referencing another chart, I'm seeing chart11.data doesn't have a value attribute, and none of the other attributes appear to contain the final aggregated counts for each category. Any advice on actually reaching these calculated values to apply score to?

4

I use a transformer to turn my query data results into a chart friendly format. That's the "chart_data" that I referenced in the code snippet above.

So, a query that returns a tabular style of data might be transformed into an array of values grouped by a property, like category name, or score, and then I use the value of this transformer in my charts. I'm often using the lodash _.keyBy or _.groupBy functions for this.
This also means I can run the transformer many times to test that the output is what I want the chart to show without having that annoying situation where it won't display anything if there's a syntax error, as you described.

I find this easier than writing the data transformation logic into the chart definition itself, but if your query is returning a structure that the chart can use with then that will work too.

5

It's a daunting task to create transformers for every chart I need, as I mentioned there are many charts to display this survey data (20+). It seems the grouped counts displayed in the chart aren't available in the Retool chart component and as using texttemplate to reach each {x} does not allow calculation on the values, I need to get creative. I'll try writing transformers to process the data and return an array of percentages for each chart which I can reference with text. I'll leave the data transformation logic in chart definitions for now, as being somewhat new to Javascript, Retool, and Plotly.js it would probably take me a lot of time to rework all the charts to pull data from transformers.

6

Serious newbie moment, but I'm struggling to find a solution. How can I import the lodash library (explicit steps including the URL you grab lodash from, please)?

7

I wrote a transformer to return an array of the percentages as strings. Sadly, it seems the text attribute doesn't understand how to use an array correctly. Skipping using the transformer value to eliminate variables, if I set "text": ['a', 'b', 'c', 'd', 'e', 'f'] I see the following ridiculous result:
Screenshot 2023-02-16 at 3.49.11 PM

8

Ah ok, couple of things here:

  • lodash is included by default in Retool, you don't need to do anything to import it, which is nice! Scripting Retool

  • Creating a transformer for each chart does sound daunting, but I'd probably recommend you play around with one transformer to get the data structure correct for one chart and then you could update the chart definitions directly with whatever functions/groupings/logic you create

  • That chart with the strange text would appear to be because your data source has many data points in it (ie an array of every score) and the chart is grouping these data points. The issue with text as an array is that it would then need a value for every one of these, not just the grouped ones. eg if your x series values are [1,2,3,4 ... 999] then you would need the text array to have the same number of elements. The workaround might be to calculate the grouped values outside of the chart itself (aggregates are deprecated in Plotly anyway so might break one day )
    This is where your functions or transformer might come in, your x series could be the summed values not all the values. This is what I ended up doing - calculate my chart data outside of plotly then just use plotly to display it.

Don't know if that made sense or not! Like I said early, working with plotly is hard enough, it can sometimes be even more challenging inside retool

1 Like
9

Thank you for the response, that does make sense to me. Luckily to calculate percentage I had to calculate totals too, so hopefully with some tweaks I can transition from having data transformation logic in the chart to simply referencing the appropriate array of totals from the transformer.

10

Hello! Thank you again for your help on this. I was easily able to adjust my transformer to return an object of arrays for my y-axis labels, counts, and percentage labels. All is working well and I have been able to make small adjustments to the duplicated transformer to support all of my charts. For closure, here's my final data JSON and the resulting chart:

[
  {
    "name": "Count of Participants",
    "x": {{healthOverallAgg.value.counts}},
    "y": {{healthOverallAgg.value.responses}},
    "type": "bar",
    "orientation": "h",
    "text": {{healthOverallAgg.value.percentages}},
    "textposition": "auto",
    "marker": {
      "color": "#033663"
    }
  }
]

Screenshot 2023-02-17 at 4.11.31 PM

11

yay! Looks good, glad you got it working :slight_smile: