Week Numbers (1-53)

Today the extract() function does not support extracting the ‘week number’ from a date field. As a workaround you can use a formula like this (may need some adjustments +/-1 for certain years, could add some ifElse logic to account for that). FYI this uses weeks starting Sun.

dateDiff(truncDate("WK", truncDate('YYYY', {order_date})), {order_date}, 'WK')+1


Hi Jesse, thanks for providing this solution! Is there a way to get the week to start on Monday? I have had some trouble in the past with trying to align Quicksight with data from Athena, which does not have support for weeks starting on Sunday.

Presto documentation
%V Week (0153), where Sunday is the first day of the week; used with %X
The following specifiers are not currently supported: %D %U %u %V %w %X

Nice, that helped a lot!

For the week starting on Monday:

    extract("WD", Date) = 1,
    dateDiff(truncDate("WK", truncDate('YYYY', Date)), Date, 'WK'),
    dateDiff(truncDate("WK", truncDate('YYYY', Date)), Date, 'WK')+1

This can be changed to any day of the week can be used as starting point.


It did not seem to work for me. It shows Dec 26, 2022 as Wk53 however it is Wk52. Any ideas? Am i missing something?

Depending on the year you may need to add/subtract 1 from the result. Stay tuned for a new function called WEEKNUM() soon though :wink:


Hey Jesse is there a way I can subscribe to updates to QuickSight like the WEEKNUM() function? This is something I am looking forward to

Best way for now is you can subscribe to notifications on this page: What's New / Blog - Amazon QuickSight Community

Also when you log into QuickSight you should see a what’s new popup if there are new features.

1 Like

There’s also this page but I dont think you can subscribe to it:

Hello @Shokat,

Not sure if you already found the answer, even I faced the same issue, I tried the below one and that worked for me.

First I created the below calculated field for the week starting from Monday


addDateTime(1,‘DD’, truncDate(‘WK’,ifelse( extract(‘WD’,{date}) = 1, addDateTime(-1, ‘DD’,{date}), {date})))

Then to extract the week numbers


ifelse(extract(‘WD’,truncDate(‘YYYY’, weekmonday))=1,
dateDiff(truncDate(“WK”, truncDate(‘YYYY’, weekmonday)), weekmonday, ‘WK’)+1,
dateDiff(truncDate(“WK”, truncDate(‘YYYY’, weekmonday)), weekmonday, ‘WK’))

Hope this helps!


Thanks for posting this solution @Shokat! :slight_smile:

Thanks a lot, works for me! :slight_smile:

1 Like