I believe it should work, but I think to change the load order of a field, it needs to be created with that sort order. week was created before this load and will use the load order from it's previous table, but if you create a new field like Week_Sort, it will be sorted with the new order.
Also, you cannot use a calculation in order by, so better to create a field and use that in your order by clause
Mod(AbsenceWeek-31, 52)+1 as TempField
week as week_sort
Order by TempField;