Week of the Year- The ISO Way

[intapiuser]

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^th ,2^nd and 3^rd to belong to the first week of 2016 and the 4^th 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))

END

Output:

 

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.

Based on the ISO week date:

https://en.wikipedia.org/wiki/ISO_week_date