Forum Discussion

EZgrafton's avatar
EZgrafton
Advocate II
10 years ago

Sort by another column error

Hello,

 

I am trying to sort a visual by another column, but I get the following error message:

 

the visual shows terminated employees by month, for the most recent 12 rolling months. I need the visual to start with May 2015 and end with April 2016. I have created a MonthYear sort column. Not sure why it is not working.

 

Any help will be greatly appreciated! :)

5 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    EZgrafton As far as I know to be able to sort by other column, you can't have more than one value in the column you're trying to sort by for the same value in column you're sorting. Have you checked that you've got that right. 

  • Narendra's avatar
    Narendra
    Microsoft Employee
    10 years ago

    Hi ,

    You can create the customer column with format like YYYY/MM-- 2016/05 in your view , Now you can you use these column for sorting.

    • EZgrafton's avatar
      EZgrafton
      Advocate II
      10 years ago

      As stated in my original message, I have created a MonthYear sort column formatted YYYYMM and I used this new column for the sorting. That's when I get the error message. Any suggestions? Thank you.

      • hmokkapati's avatar
        hmokkapati
        Helper II
        9 years ago

        Hello,

         

        I have also created column YearMonth (201608) to be used for sorting purposes and when i try to use this column for "Sort by column" i get the same error you mentioned. Were you able to find any solution for this? Any pointers appreciated.

         

        Thank You,

        Harisha

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    This looks like an old post.. but adding this here in case somebody else is looking for an answer.  Basically, you need to have the same sort order value for every month (or value) that is the same name.  We have a time table, so for every month the sort value needs to be set the same.  We added Month_Period_Of_Time and Month_Period_Of_Time_Reverse.

     

    PK_Date    Month    Month_Name    Week    Week_Name    Month_Period_Of_Time    Month_Period_Of_Time_Reverse
    2017-01-28 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-23 00:00:00.000    Week 5, 2017    1462    1431
    2017-01-29 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-23 00:00:00.000    Week 5, 2017    1462    1431
    2017-01-30 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-30 00:00:00.000    Week 6, 2017    1462    1431
    2017-01-31 00:00:00.000    2017-01-01 00:00:00.000    Jan 2017    2017-01-30 00:00:00.000    Week 6, 2017    1462    1431
    2017-02-01 00:00:00.000    2017-02-01 00:00:00.000    Feb 2017    2017-01-30 00:00:00.000    Week 6, 2017    1493    1403
    2017-02-02 00:00:00.000    2017-02-01 00:00:00.000    Feb 2017    2017-01-30 00:00:00.000    Week 6, 2017    1493    1403
    2017-02-03 00:00:00.000    2017-02-01 00:00:00.000    Feb 2017    2017-01-30 00:00:00.000    Week 6, 2017    1493    1403
    2017-02-04 00:00:00.000    2017-02-01 00:00:00.000    Feb 2017    2017-01-30 00:00:00.000    Week 6, 2017    1493    1403
    2017-02-05 00:00:00.000    2017-02-01 00:00:00.000    Feb 2017    2017-01-30 00:00:00.000    Week 6, 2017    1493    1403

     

    We found this magic sql to set the values.. sorry, we could not find the original link back to it.


    --Month
    UPDATE dbo.Time SET Month_Period_Of_Time = RowNumber
    FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY  [Month]) AS RowNumber FROM dbo.Time) AS TMR
    WHERE TM.[Month] = TMR.[Month]

    UPDATE dbo.Time SET Month_Period_Of_Time_Reverse = RowNumber
    FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY  [Month] DESC) AS RowNumber FROM dbo.Time) AS TMR
    WHERE TM.[Month] = TMR.[Month]