Skip to main content
Question

Sorting calendar week

  • November 11, 2019
  • 2 replies
  • 28 views
J
Level 4
Hello all, how is it possible that the combination of calendar week and year is sorted correctly on my timeline? Starting with 1-2019, 2-2019, 3-201944-2019, 45-2019 image1338212 24.1 KB Many thanks! Best regards, Jonas

2 replies

D
Level 1
Forum|alt.badge.img+2
Hi Jonas, there are two options:
  1. You can prepend a 0 if the week is smaller than 10: CASE WHEN CALENDAR_WEEK(VBAP.ERDAT)<10 THEN 0 ELSE END || CALENDAR_WEEK(VBAP.ERDAT) || - || YEAR(VBAP.ERDAT)
  2. The other option would be to use this formula: ROUND_WEEK("VBAP"."ERDAT"), and then specify this format pattern: %U-%Y , which formats the date returned by ROUND_WEEK as [Week]-[Year]. With this solution however, your numbers will change a bit, because both solutions differ in how the week of the years is determined (In CALENDAR_WEEK; the first week of the year is the week with January 4th, you dont have this behavior in option 2. So the first week and then also all subsequent weeks might be shifted.)
Best David
J
Level 4
  • jonas.schei11
  • Author
  • Level 4
  • November 12, 2019
Hi David, many thanks for your reply. I adjusted the first option a bit and now get the right result. image101446 3.84 KB Thanks for your help. Best regards, Jonas
Community Code of ConductAccessibility statement