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 ne...
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]