Wrong sorting of colours in bar chart

I have a bar chart in Canvas for which I want to give each bar a separate colour in a gradient sorted in the same way the bar chart is sorted, example:

My code:

| essql 
  query="select top 10 \"sales.representative.name\" as salesRep, sum(expectedRevenueInSEK) as revenue, 'SEK' as currency from \"reporting-merchants*\" where \"sales.representative.name\" is not null group by \"sales.representative.name\" order by revenue desc"
| mapColumn name="results" 
  expression={string value={getCell "currency"} " " {getCell "revenue" | formatnumber "0,0" | replace pattern="[,]" replacement="."}}
| pointseries x="salesRep" y="revenue" color="revenue" text="results"
| plot legend=false 
  font={font align="center" color="#000000" family="Helvetica, Arial, sans-serif" italic=false size=14 underline=false weight="normal"} xaxis=false yaxis=false 
  palette={palette "#42D185" "#39B877" "#31A06A" "#2A885C" "#24714E" "#1F5B40" "#1A4632" "#153225" "#101E18" "#020805"} defaultStyle={seriesStyle bars="1"}
| render containerStyle={containerStyle} css=".canvasRenderEl .valueLabel {
  margin-top: -14px !important;

This comes out like:


I put the colour on the revenue which is, I think, sorted alphanumerical instead of numerical so it does not get the nice gradient as we want to

I tried a workaround such as adding ROW_NUMBER in the sql query to get something that can be sorted on but that is not known in elastic sql

Any advice on how this can be achieved?

Don't know if it is the right way to do it, but since your data is already sorted, you can add a ROW_NUMBER via mapColumn using this technique :

| var_set name='current' value=0
| mapColumn name="row_number" expression={var name='current' | var_set name='current' value={math expression='add(value, 1)'}}

Awesome @charlesfr.rey this was exactly what I was looking for and failed to come up with. thanks!

(And I saw your other solution too, will be trying that soon!)

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.