Wrong sorting of colours in bar chart

[JeroenK (Jeroen de Koning)]

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:

filters
| 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?

[charlesfr.rey]

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)'}}

[JeroenK (Jeroen de Koning)]

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!)