Forum Discussion
Sorting a Parent - Child Hierarchy
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!
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 thenValue 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?
5 Replies
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!
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?
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 thenValue 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?
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.
SourceOutput
