Sorting calendar week

#1

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-2019…44-2019, 45-2019

image
image1338×212 24.1 KB

image

Many thanks!
Best regards,
Jonas

#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 don’t have this behavior in option 2. So the first week and then also all subsequent weeks might be shifted.)

Best
David

#3

Hi David,

many thanks for your reply. I adjusted the first option a bit and now get the right result.

image
image1014×46 3.84 KB

Thanks for your help.
Best regards,
Jonas