KB485021: A possibility to calculate calendar weeks following the ISO 8601 standard in MicroStrategy.

Symptom

In year 2021 a user might notice that the number of the week in the year calculated in Strategy differs from the number of the week returned from other sources (for example the Outlook calendar).
An example, according to the Week(day) function in Strategy, March 9 is in Week 11:

Whereas the actual week of March 9 is Calendar Week 10:

Root Cause

The issue is caused by the fact, that Strategy’s function Week() does not follow the ISO 8601 Week norm (Week 1 always contains 4 January) which is being used in multiple countries. Instead, it follows the U.S. system, where Week 1 always starts on January 1st. This means that there would be years with a discrepancy of one week between two systems. One such year is 2021. A difference between two systems can be seen in the screenshot below:

Weeks for year 2020 are the same. Starting from 1 January there is a difference of one week.
 

Action

Currently a possibility of introducing an out of the box ISO-Week function is being reviewed by Strategy.

Workaround

As a workaround a user can use a custom function calculating numbers of weeks following the ISO 8601 norm:

Int(((DayOfYear(((Int((DateDiff(Date@ID;"02.01.1990";"d")/7))*7)+5))+6)/7))

This function would be valid not just for 2021 but for other affected years as well. It is based on a similar solution for SQL Server from: https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
It consists of the following steps:

• Step 1 – Calculate what the Monday of the Week for the Current Date is:

DateDiff(Datum@ID;"02.01.1990";"d")

• Step 2 – Calculate what the Thursday of the Week for the Current Date is:

(DateDiff(Datum@ID;"02.01.1990";"d")/7))*7)+5

•  Step 3 – Convert the Date Serial to the "Day of the Year"

DayOfYear(((Int((DateDiff(Datum@ID;"02.01.1990";"d")/7))*7)+5))+6)

• Step 4 – Divide the "Up-Rounded" Number of Days by 7 to Get the ISO Week Number

Int(((DayOfYear(((Int((DateDiff(Datum@ID;"02.01.1990";"d")/7))*7)+5))+6)/7))