ISO WEEK of the year

Question & Answer
1

I found out the formula to calculate week of the year in this forum and tried PFA, when I tried to debug I found out that for some weeks it is giving me the wrong result,
I cross-checked with the iso week calculator. PFA, any work around for this?

11
111291×487 115 KB

2

Is this only failing for 2020? Does it work for other years? Also with your iso week calculator, does it start on Monday / Sunday?

3

Yes, starts from Monday

4

ISO Week-Year Calculation for AWS Quicksight (as of 30 July 2021)
This gists is an attempt to ‘hack’ or a workaround for making the starting week as Monday (ISO 8601), retrieving an ISO week number and the corresponding year of ISO week number As there’s no such function per 30 July 2021 to perform any extraction of week number in Quicksight, the following are supposedly resolve such cases.

Start week is Monday (Ref #2)
Define a column with the following formula, for this usage it will be named week_start_monday

There’s an edge case handling for when the date of interest is Sunday. When the day is Sunday, it should belong to the previous week instead of the next one (since the week runs from Monday-Sunday, instead of Sunday-Saturday)

addDateTime(1,
‘DD’,
truncDate(‘WK’,
ifelse(extract(‘WD’,{date}) = 1,
addDateTime(-1, ‘DD’, {date}),
{date}
)
)
)
Thursday of the Week (Ref #3)
Instead of following the literal step listed in Ref #3, we can utilise the truncDate function which present in Quicksight instead of the backtracking needed to the 1st Jan 1900 as a reference point. As indicated in the ‘First week’ section of Ref #1, the Thursday date is crucial to determine the ISO week number.

Following the Ref #3 notion, create a column named as DoYThursday_date since it’s the date instead of the day number of the year.

addDateTime(3,“DD”,{week_start_monday})
ISO Week Calculation (Ref #3)
This is where the calculation of the ISO week number is done. Explanation of the step by step of +6 covered in the reference link. This can be done by the following calculation that will be created in the column named iso_week

floor((dateDiff(truncDate(‘YYYY’,{DoYThursday_date}), {DoYThursday_date}, “DD”)+6)/7)
ISO Week Year (Ref #4, #5)
The year corresponding to the ISO week not necessarily the year of the date. With that, another calculation need to be done to retrieve the correct ISO week year. This can be done by the following calculation that will be created in the column named iso_week_year

extract(“YYYY”, addDateTime(26 - {iso_week}, “DD”, date))

I got this on github, seems working fine

5

link

1 Like