I have a nested axis that I would like to sort. This is something that is not supported - but does anyone know of a workaround for this? I was hoping that if I hid the subtotal on each line item it would allow the sort, but it does not.
If there is no workaround, I hope this is something that can be considered for the future.
RE: Sort work with multiple columns (nested dimensions)
There's a workaround for this: You need to create a new line item called Sort Order, which combines the two dimensions you want to sory by, in the order you want.
You can do this with the RANK() function, for example
1000 * RANK('Line Item 1) + RANK('Line Item 2')
...as long as you know the weightings to apply (e.g. here I've multiplied by 1,000 because I know there won't be more than 1,000) items in the list.
Alternatively, you can do it with custom text-conversion logic, for example
Re: RE: Sort work with multiple columns (nested dimensions)
Thank you for your answers. However I don't think it will solve my issue as I need to display the two nested dimensions in a dashboard and in this case I cannot filter. In addition I need to sort only the second dimension (within the first one) alphetically.
The first dimension is the expense group based on the GL account. it's a very stable dimension, it changes very rarely. The second dimension is the vendors list. This list is very dynamic. I have created a mecanism (logic + actions) for the end-users to add new vendors, but unfortuntely each time a new member of this vendors list is created it's showing up at the bottom of the list. The consequence is that in the dashboard the vendors list is not sorted alphebetically.
The only option that I have now is to move new vendors from the bottom of the list manually to make sure all vendors are sorted alphabetically. Not ideal!
(see screenshot below - Vendors are currently sorted alphabeticaly because I've done it manually - However I looking for some sort of automation)