Nested Axis Sorting

brendan_morriso

Hi

 

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. 

 

Thanks

Brendan

peter_mcanena

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

TEXT(YEAR(Date)) & "-" & TEXT(MONTH(Date) + 50) & "-" & TEXT(DAY(Date) + 50) & " " & TEXT(Number + 1000000)

or any combination of the two methods. Just make sure, whatever combination you use, you resolve to a consistent data type - either text or number.

Sheethal

There is a old post which suggests a workaround. Not sure whether it will help you but posting it for your reference.

 

https://community.anaplan.com/t5/Product-Requests-for/Sort-work-with-multiple-columns-nested-dimensions/m-p/3043

brendan_morriso

Thanks for the reply and information!

I will see if this will help, but in my use case it may not work.

Hopefully there will be native functionaltiy added around this in the future (also multi-column sorting). 

NathalieLbn

Hi,

 

It seems the link for the workaround doesn't work anymore.

Does somebody know the workaround? 

 

Thanks,

Nathalie

KayneSchwarz

@NathalieLbn, 

This is the content from the original link above. Does this help?

DavidSmith

@NathalieLbn 

You are probably aware of our new understanding of the impact of text and strings in particular; this article was from 3 years ago before we fully understood the impact

See https://community.anaplan.com/t5/Best-Practices/Formula-Optimization-in-Anaplan/ta-p/41663

for more details

 

So if you do need to do something like it, please make sure, the joins and the details of the calculations are done in the most appropriate and smallest possible modules

 

David

NathalieLbn

Hi All,

 

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)  

 

Thanks for your help,

Nathalie

 

rob_marshall

@NathalieLbn ,

 

Not exactly what you want, but if you re-pivot your module to have your Accounts in the page axis (since you already have that as slicer), it will work.

 

 

 

Hope this helps,

 

Rob

NathalieLbn

Thanks!

Unfortunately the end user want to see at once accounts and the total by expense type (which is the parent of the account), I cannot pivot here...

 

Thanks,

Nathalie

 

katherinesmith

I was hoping to see a better solution in this thread, but just in case, the only way I've found  a work around is literally uploading the list in the sort I wanted. So for example, If I have a sales hierarchy that I want sorted by hire date, I sort my load from my employee flat list in the hire date order, so it's statically sorted in the way I need. This all goes out the window once you have to add more records after the initial load... unless you can do a complete wipe and replace of the list 😕 But if you're desperate (as I was), it works ok haha

DavidSmith

@katherinesmith 

We are developing the ability to sort a list based on a line item through an action - Doesn't solve for all of the issues mentioned, but will help in a lot of cases

David

Donghui

how about trying to use “show” function. the order of selecting line items decide the final showing order.

JRRajavarman

What if making a line item subset from the source module, now we have a line items as dimension and try to apply the Sort on top of it.