Forum Discussion
Sorting on columns in a matrix
Pic will make it clear :
What I wanted is Year-week in a descending order.
At leat putting Year-Month above is will get me the descending part because YM is sorted by YM- :
)
But for the weeks it is not fully working (but now at least 2025 comes first because of YM)
multiply by -1 is not an option I think because I use this to get the week numbers:
Hi rpinxt
The matrix visual does not offer an option to sort columns in descending or ascending order directly. Columns are sorted either alphabetically or based on custom sorting in ascending order. To achieve your desired sorting, you will need to create a custom sort column. Please refer to the example below:
DatesTable = VAR _base = ADDCOLUMNS ( CALENDAR ( DATE ( 2024, 1, 1 ), TODAY () ), "YYYY-MM", FORMAT ( [Date], "YYYY-MM" ), "Year Week", YEAR ( [Date] ) & "-" & FORMAT ( WEEKNUM ( [Date] ), "00" ) ) RETURN ADDCOLUMNS ( _base, "Year-Month Sort", RANKX ( _base, [YYYY-MM],, DESC, DENSE ), "Year-Week Sort", RANKX ( _base, [Year Week],, DESC, DENSE ) )
5 Replies
Hi rpinxt
The matrix visual does not offer an option to sort columns in descending or ascending order directly. Columns are sorted either alphabetically or based on custom sorting in ascending order. To achieve your desired sorting, you will need to create a custom sort column. Please refer to the example below:
DatesTable = VAR _base = ADDCOLUMNS ( CALENDAR ( DATE ( 2024, 1, 1 ), TODAY () ), "YYYY-MM", FORMAT ( [Date], "YYYY-MM" ), "Year Week", YEAR ( [Date] ) & "-" & FORMAT ( WEEKNUM ( [Date] ), "00" ) ) RETURN ADDCOLUMNS ( _base, "Year-Month Sort", RANKX ( _base, [YYYY-MM],, DESC, DENSE ), "Year-Week Sort", RANKX ( _base, [Year Week],, DESC, DENSE ) )I think I understand a bit danextian
My original Dim_Date table was connect to a table on rundate then from that connection it makes:
Dim_Date =ADDCOLUMNS(CALENDARAUTO(),"Year", YEAR([Date]),"Quarter", FORMAT([Date],"\QQ"),"Quarter Nr", QUARTER([Date]),"Month", FORMAT([Date], "mmmm" ),"Mth", FORMAT([Date], "mmm" ),"Month Number", MONTH([Date]),"Week Number", WEEKNUM([Date]),"Week Number Txt", "Week " & WEEKNUM([Date]),"Week Nr (ISO)", WEEKNUM([Date], 21),"Week Nr (ISO) Desc", WEEKNUM([Date], 21),"Year - Week (ISO)", YEAR([Date]) &"-"& WEEKNUM([Date], 21),"Year - Week (ISO)2", FORMAT([Date], "-YYYY-WW"),"ISO Week Desc", WEEKNUM([Date], 21) * -1,"Week Day", FORMAT([Date], "DDD"),"Week Day Nr", WEEKDAY([Date], 2),"YM", FORMAT([Date], "YYYY-MM"),"YM-", FORMAT([Date], "YYYY-MM") * -1,"Period", FORMAT([Date], "MM-YYYY"))Now for the matrix I made a new date table names Dim_Date_Matrix and also connected to the Rundate.This looks like :Dim_Date_Matrix =VAR _base =ADDCOLUMNS (CALENDAR ( DATE ( 2021, 1, 1 ), TODAY () ),"YYYY-MM", FORMAT ( [Date], "YYYY-MM" ),"Year Week",YEAR ( [Date] ) & "-"& FORMAT ( WEEKNUM ( [Date] ), "00" ))RETURNADDCOLUMNS (_base,"Year-Month Sort", RANKX ( _base, [YYYY-MM],, DESC, DENSE ),"Year-Week Sort", RANKX ( _base, [Year Week],, DESC, DENSE ))Still don't fully understand but the Today() probably makes for suddenly week 2 is showing up.The max for rundate at this time is 5th of Jan.So in the original Dim_Date that leeds to Week 1.CALENDARAUTO relies on the existing dates in your model. I don't know what dates you have there. So when you use TODAY() in CALENDAR(), you're seeing week 2 since we already are in the second week of January. Instead of today, use MAX(data[rundate])
