Chart changes colours with new filter values


I created a sql question and assigned certain colours to the bars in my chart (Project should be always green for example).

But when I change the filter variable the colours are switching back to the default values:

When I change the filter back my preferred colours are back.

It looks like the order of my categories is changing and the colours are not assigned to a category but to the place the category has in the order?

Even though I did order by catgory ascending…

Looks like a bug?

You may be able to preserve the colours by preserving the order of your categories. You can do this with ORDER BY in your SQL, although that can take some gymnastics depending on your query.

EDIT: Ooops just noticed you said you’re already doing ordering. Are you sure your categories are returned by your query in the same order?

thanks for your reply - yes I have ordered them and they go back to the original order when I change the filter back to the “normal” filter.

One idea I had was that I can order the categories differently, something like
ORDER BY category = "Project, category = “FundraisingEvent” …
But I haven´t tried that yet, therefore not sure if it would work.
And it would be unhandy if you assume that you could get new categories coming along with new data. You would have to change the code all the time.

I still think its a bug though, when a new filter value ignores the order by function?
Thanks, Eva

Just checked and my idea isn´t working - the order by category is ignored when I write it that way:
ORDER BY category = "Project, category = “FundraisingEvent” …

Thanks for any ideas or a bug file

I wonder if the ordering of your results is not what you think it is? I say this because we’ve been able to retain consistent coloring by ensuring consistent ordering of our results. I’m not sure what database you’re using, but in PostgreSQL one way to force explicit arbitrary ordering by category is to do something like this: ORDER BY array_position(array['Project', 'FundRaisingEvent', 'SomethingElse'], category)