Sort by another column error

[EZgrafton]

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! 🙂

[Anonymous]

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]

[Narendra]

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]

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]

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

[ankitpatira]

@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.