Custom Sort of "Day of Week" Calculated Item

[blewsas]

I have created a "Day of Week" calculated item from a date field.  I'd like to apply a custom sort so that the first day of the week is Monday, rather than Sunday.  However, when I right-click on the "Day of Week" category, the "custom sort" option is not available.  Any ideas?  SAS VA 8.3

[FredrikE]

Hi!

Are you creating a string variable with the weekday name?

If not maybe you should try that, it might solve both the sorting and the difference between the editor and report mode.

//Fredrik

[blewsas]

my calculation is

  DatePart('Offence TMST'n)

And I am applying the "Day of Week" format to this.

What should I be doing differently?

Thanks!

[blewsas]

I deleted the old data item "Offence Day of Week" and created a new one by right-clicking on the "Offence TMST" (datetime) data item and selecting "Duplicate Data Item".  I renamed the new item to "Offence Day of Week" and applied the "Day, Date" format. 

That gives me what I want -  "Offence Days of Week" of "Sunday", "Monday", "Tuesday", etc.  However, I have the same problem when I view the report - they change to "03Jan1960", "04Jan1960", "05Jan1960", etc.

And I can't custom sort them.

Help!  Very frustrating!

[blewsas]

That gives me what I want -  "Offence Days of Week" of "Sunday", "Monday", "Tuesday", etc.  However, I have the same problem when I view the report - they change to "03Jan1960", "04Jan1960", "05Jan1960", etc.

And I can't custom sort them.

Help!  Very frustrating!

[FredrikE · Accepted solution]

Hi!

Try this 🙂

Create a new calculated item (character) and add this code:

IF ( DayOfWeek('Date'n) = 1 )
RETURN 'Sunday'
ELSE (
IF ( DayOfWeek('Date'n) = 2 )
RETURN 'Monday'
ELSE (
IF ( DayOfWeek('Date'n) = 3 )
RETURN 'Tuesday'
ELSE (
IF ( DayOfWeek('Date'n) = 4 )
RETURN 'Wednesday'
ELSE (
IF ( DayOfWeek('Date'n) = 5 )
RETURN 'Thursday'
ELSE (
IF ( DayOfWeek('Date'n) = 6 )
RETURN 'Friday'
ELSE (
IF ( DayOfWeek('Date'n) = 7 )
RETURN 'Saturday'
ELSE '-' ) ) ) ) ) )

Then add the custom sort.

//Fredrik

[blewsas]

Thanks, that would work.  I took a different, but similar, path - I created a custom category called "Offence DOW" based on my "Offence Day of Week" values and used them instead.