Sorting a Parent - Child Hierarchy

[CVance]

I'm trying to sort the children in a hierarchy in a matrix visualization. From what I've found so far, the children are always sorted alphabetically. I need to sort the children non-alphabetically. In the following example, the children of the Top Parent should be sorted as shown rather than having the first child node be AA Third Child / Parent. Likewise, all children should not be forced into a alphabetical sort.

• Top Parent
• First Child / Parent
• First Child of First Parent
• A Next Level Child
• A Second Child
• AA Third Child / Parent
• First Child of AA Third Parent

The only solution I've found so far is to pre-pend numbers to the children so the alpha sort honors my desired sort.

• Top Parent
• 1 - First Child / Parent
• 1 – First Child of First Parent
• 2 - A Second Child
• 3 - AA Third Child / Parent
• 1 – First Child of AA Third Parent

This is not an option for me as this is customer facing and the node names and sort order are specified by the customer.

I've added an index column to the model data, so I know each row (parent/child) has a unique index value, and when I use a table visualization, it is sorted by the index column as you would expect. The issue is that it's not a hierarchy.  ugh!

Any advice is greatly appreciated!

Thanks!

[Sivakumar_H]

Hi @CVance 

Try to create two seperete sort columns for Year and Weekend date and Sort Year to Year sort column and Weekend Date to Weekend Date Sort column. This should work and we can order any type ascending or descending to the columns which we required.

Say for example we want hierachy order as A-P1,A-P3,A-P2 for all the parent values then we can create sort by column according to that required order.

Source

Output

[sevenhills]

Looks like you have done correctly - all steps needed for the custom order of items.

Quick check, can you see the sort by column is index? if not assign.

Sample: For the basename column, I used sort by column as index

Hope it helps!

[CVance]

@sevenhills

Thank you for your quick reply! 

I checked and the name column is sorted by the Index column. I wish your suggestion worked, but it does not work. Here are some screen shots showing the sort by column is set correctly, and the results shown in a matrix visual and table visual.

Screen shot showing data in table is sorted by the Index column:

and finally a screen shot showing the matrix not sorted by Index, but the table visual is:

I need the matrix visual (hierarchy) to follow the Index column sort order (specifically OSB Account 1 should be before Net Loans).

Any other ideas?

[sevenhills · Accepted solution]

Thanks for sharing in details and see that you are doing the Balance sheet, which makes it easy to think and based on your screenshots, it should be easy fix. 

I am assuming that you are using individual columns for the hierarchy such as        Balance Sheet Level 1, 

    Balance Sheet Level 2, ... (check the fields list in the matrix > Rows). If yes,

a) Please could you check the sory by columns used in the hierarchy uses the correct sort by column.

Say, 

Balance Sheet Level 1 ==> BB Sheet Level 1 Sort Order

Balance Sheet Level 2 ==> BB Sheet Level 2 Sort Order

Balance Sheet Level 3 ==> BB Sheet Level 3 Sort Order

b) Check the data in these columns are according to your sort order needs. 

BB Sheet Level 1 Sort Order

BB Sheet Level 2 Sort Order

BB Sheet Level 3 Sort Order

Test: Per your screenshot, check the data in the "BB Sheet Level 2 Sort Order" column. 
      Value for the row "OBS Account 1" has to be lower then 

      Value for the row "Net Loans" 

Tip: To test, you create a matrix and drag the fields one by one. 

            Balance Sheet Level 1

            Balance Sheet Level 2

            Balance Sheet Level 3

Let me know whether it works or not?

[CVance]

You definitely put me on the correct track!  Thanks!!

The Sort Order columns were not correct, so when I tried to use the Level 1 Sort Order column to sort the Level 1 column, I got an error saying (basically): An item can't have more than 1 sort order value. Ok, I get it.
Eventually, I found a post that explained a solution for that, and now I have it working. The post I used is https://www.esbrina-ba.com/sorting-a-parent-child-defined-hierarchy/. Excellent post!

Again, thank you for your quick and very helpful responses!