Forum Discussion
Advocate Vmatrix table: sort columns in descending order
Hi,
You cannot achieve this reverse sorting directly... here is a workaround:
1. Go to the Edit Query option of the Week dim table.
2. Apply Sort desc on the unique week code column (there must be a unique identifier for each week i.e. in my case I have WeekCode column as 20170101, 20170102, 20170103, 20170104, 20170205, 20170206.... YYYYMMWW where WW is the incremental week number of the year from 1 to 52.
3. Select 'Add Index column' from Add Column menu and provide Starting Index=1 and Increment=1 in the dialog box.
4. Rename column as appropriate.
4. Save Apply and Close the Edit Query
5. Use this Index as 'Sort By Column' wherever required.
Hi ipsingh79,
I tried your suggestion but get an message saying
Sort by another column
We can't sort the 'Date' column by 'Index'. There can't be more than one value in 'Index' for the same value in 'Date'. Please choose a difference column for sorting or update the data in 'Index'.
Based on this message, I duplicated the 'Date' column, set Data Type: Whole Number, and sort 'Date' by this new field, but the Matrix still displays the oldest Date on the Left.
Hi Ink20a2,
My bad I forgot to update the post.
So here is the working solution:
1. Make sure your table has some column as FinancialWeekCode i.e. YYYYMMWW where WW is the WeekNo e.g. ...,20170103, 20170104, 20170205,...
2. Add custom column as : SorttWeekCode = 99991253 - [FinancialWeekCode]
3. Apply and Close the query editor.
4. Sort the Week Name column with SortWeekCode custom column.
Let me know if you still get any issue.
Thanks ipsingh79 that worked. I had been missing the step 2. as adding index hadn't worked, but the subtraction in that step gave the higher number to sort on. Brilliant!