Week Numbers (1-53)

Learning Center Articles
, ,
1

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

5 Likes
2

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 (01 … 53), 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
https://prestodb.io/docs/current/functions/datetime.html

3

Nice, that helped a lot!

For the week starting on Monday:

ifelse(
    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.

4 Likes
4

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?

5

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:

4 Likes
6

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

7

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
8

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

9

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

weekmonday

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

Then to extract the week numbers

WeekNumber

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
Sushma

3 Likes
10

Thanks for posting this solution @Shokat! :slight_smile:

11

Thanks a lot, works for me! :slight_smile:

2 Likes
12

@sushma.sreella That worked for me! Thanks very much.

1 Like