ISO Weeks in CRM Analytics - Everything You Need to Know
Some articles on this blog become outdated over time, and I hope this one will too.
As of January 2024, standard date formats don’t follow the ISO standard for the week of the year in CRMA. Since the ISO standard is widely used by companies, especially at the beginning/end of the year, we’re asked about it.
What is the system in CRMA?
In CRMA, Week 1 of the year always starts on January 1. By default, weeks begin on Sunday, but this can be changed. The first and last weeks of the year may have fewer than 7 days.
ISO Standard is different and the main characteristics are:
- Weeks start on Monday and end on Sunday.
- The first week of the year must include the first Thursday of the year.
- Week formatting is either “YYYY-Www” (e.g., 2021-W37) or “YYYYWww” (e.g., 2021W37). For this exercise, I'll create two fields for better grouping.
Let’s go to the recipe
In the code below, I’ll show the formula that you can use in recipes to get the ISO week for any date, without using CSV tables, connections to external systems, or workarounds with formula fields in Salesforce.
Good news is that you don’t need to understand the formulas, just copy/paste and replace dummy field name with the real one.
I’ll also walk through the steps for you to better understand the logic behind.
We’ll create 2 formula fields in recipe:
for ISO week (format: “Www”, eg. “W37”)
for Year corresponding to ISO week - as sometimes first days on the new year can still have old ISO year and vice versa (format: “YYYY”, eg. “2021”)
The ISO Week formula
Given that my date/time field has API name “Date__c”, here is the formula:
format_number(weekofyear(Date__c), ‘W00’)
Where weekofyear() formula is basically ISO week field as SF says: “Weeks start on Monday. Week 1 is the first week of the year with more than three days”. This is the same logic as formal ISO definition.
The format_number() function ensures that each week is displayed with two digits (e.g., "W01", "W37") to keep the order consistent. I also added a "W" at the start to follow ISO formatting. This makes it easier for users to recognize that these represent ISO weeks, which they are more likely to understand (from our experience this is common in Western Europe).
For this formula, Output Type is Text.
The ISO Year formula
Given that my date/time field has API name “Date__c”, here is the formula:
format_number(year(date_trunc(‘week’, Date__c) + interval 3 day), ‘0000’)
Here are the steps:
1. Look for the Monday of the Date’s week
Where date_trunc(‘week’, Date__c) returns Monday of the week of my date. This formula with ‘week’ format is designed to return Monday, so it makes our calculations easier.
2. Look for Thursday’s date of that week
Then, + interval 3 day enables to find the Thursday of the week of our date field. This is needed because the year of Thursday’s date define the ISO year. This logic comes from ISO definition.
Eg. 30 and 31 December 2024 although they are in calendar year 2024, they have ISO year of the week 2025 because in their week, the Thursday (02 Jan 2025) falls in calendar year 2025.
This formula then adds three days to Monday, which returns the Thursday.
3. Extract the calendar year of the Thursday’s date
This is a simple year() formula to get the calendar year.
4. Convert to text and apply formatting
I use format_number() formula here and ‘0000’ as format, so 4 characters.
Other comments
When working with CRMA, you might notice that date functions, especially those involving weeks, can give inconsistent results.
In SAQL, weeks typically start on Sunday, and week 01 of the year always begins on January 1st.
However, in recipes, weeks usually start on Monday, and ISO week numbering is used by default - just like in the formula above.
This function though isn’t documented (https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_dateFunctions.htm&type=5), and I had to do a lot of testing to confirm it works correctly. Unfortunately, there’s no formula to get the year of an ISO week, which is disappointing, and that’s why it’s presented above.
Of course I was also creating too complex (yet 100% correct) formulas, like this one:
FORMAT_NUMBER(
FLOOR(
(dayofyear(
TO_TIMESTAMP(-2208988800 +
((FLOOR(DATEDIFF( Date, TO_TIMESTAMP(-2208988800))/7)*7 + 3)*86400))) + 6)/7), '00')
not being aware that it can be done simpler.
It's a shame that SAQL and UI query editors don't natively support the ISO format. Even if you create fields in the way shown above, you can't use them as easily as other formats and it’s much more difficult for users to create their own lenses based on ISO weeks.
I hope you got the answers you needed! If you're still having trouble, just let me know, and we can sort it out together.
