Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
1 year ago
Solved

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- : Dim_Date = A...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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 )
    )

     

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
1 year ago

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" )
    )
RETURN
    ADDCOLUMNS (
        _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.
danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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])