Week of the Year- The ISO Way
If you want each date to “belong” to its “actual year” you can use the ISO way to calculate the week number.
For example, if 1/1/2016 is on Wednesday and you want the 1 ,2 and 3 to belong to the first week of 2016 and the 4 to belong to the second week of 2016 you can use this function which is based on the ISO week of year function:
CASE WHEN (4-DayOfWeek(CreateDate(getyear(date),1,1)))<0
--See comment *** below
THEN floor((tofloat(DayOfYear(date))-tofloat(DayOfWeek(date))+tofloat(10))/tofloat(7))+1
ELSE floor((tofloat(DayOfYear(date))-tofloat(DayOfWeek(date))+tofloat(10))/tofloat(7))
ENDOutput:
Keep in mind:
*You might have short weeks- One week might include only one day
**In this scenario the first day of the week is Monday
*** If the week number equals 0, it means that the given date belongs to the preceding year because the majority of the week belongs to the previous year – we added this condition to avoid that outcome.
0 comments
