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

6 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.

2 Likes
13

Did this get launched Jesse?

3 Likes
14

We found another approach:

addDateTime(6, ‘DD’, truncDate(‘WK’, {Your Date}))

→ move each date to the beginning of the week, Sunday (works on the edge of the year)
→ add 6 days to move it to last day of the week, Saturday (gets the year back for the edge cases)

This way you have nice grouping per “last day of the week”, but you can add weeknum() (not documented function) to get the number :slight_smile:

15

Thanks a ton. The fact it was posted in 2021 and still we have to use this shows how an important function like getting week num is hard in Quicksight. Hope developer teams notice this.

1 Like