How can I set multiple sort configuration on a 2 dimensions bar graph

[rdugg]

I have a sheet with a chart having 2 dimensions + 1 metric bar graph  (year, product) and Sum(sales).

I have a 2 buttons input linked to a variable (vFirstDimension) so a user can choose if he wants to display year or product as the first dimension.

If the user chooses year as dimension 1, I want the sort to be like :

increasing(year), increasing(Sum(sales))

but if the user chooses product as dimension 1, I want the sort to be like:

alphabetical-increasing(product), increasing(year).

Is there a way to achieve that ?

I see that I can choose sorting as auto or custom, and if I choose custom I can statically choose the order from the GUI.

But How can I set multiple custom order that I load from variables, or with a mechanism like bookmarks is working for custom predefined filtering of dataset ?

[JandreKillianRIC]

Hi @rdugg 

I would use sort by expression - Then testing what the value of the variable is using a if statement 

IF(vFirstDimension = 'Product', Sort like this, Sort like that) 

Regards Jandre

[rdugg]

I'm not sure about that : In the interface there is

 - Sort

   - 1.  my dimension (1)

   - 2. my dimension (2)

   - 3. my metric

and under each of my dimensions (1. and 2.  ) is a sort by expression field.

and then there is a drag'n'drop handle (visually like an "hamburger menu") to choose to chose the rank/priority of which of the 3 sorts rules go first. this rank is static and I don't see how it can be programmed dynamically.

Or perhaps should I have a kind of expression to sort on a total order that use all 3 concepts (the 2 dimensions + metric) and I put this in the sort of expression of 1. only ?

something like 

100x(sort on dimension 1 / max_sort_values_of_dimension 1)

+ 10x(sort on dimension 2 / max(sort_values_of_dimension2)

+ (sort on metric / max(sort_values_of_metric)

Because if I can do something like that, then I see how I can use the if(, sort like this, sort like that).

It's really puzzling.

[JandreKillianRIC]

Hi @rdugg 

Ok i see, Do you just want to change the way the chart is represented or just change the sorting? If you just change the sorting I will need to look into this but you can change the way the chart is represented? 

For Example (Version 1) 

Version 2

Is this what you are looking for? 

Regards Jandre

[rdugg]

i want to change the way the sorting is.

example version 2 is mostly what I want with

dimension 1 = year and

dimension 2 = products

(except that I want the sorting increasing on sales)

Example version 1 is not what I want when

dimension 1 = product and

dimension 2 = year

Because for each product I want a sort by year (as 2020, 2021, 2022), and I don't sort on the metric here.

[JandreKillianRIC · Accepted solution]

Hi @rdugg 

So something like this? 

Version 1 

Version 2 

I cant get a way to do this using a custom sort. My best guess for now would be to use the Layout container, Duplicate the chart and overlay them in the container, using conditional show you can hide / show the one you want to display. 

Regards Jandre

[rdugg]

Conceptually yes that's it (apart that in your version 1, I would have product as dimension 1 ).

Sad that it's not possible directly, but that's what I feared. Thank you for the layout container trick.