How can I extract number of week from one of my column

Question & Answer
, , , , ,
1

I want to extract number of week (from first week of the year), how can I do it?
following code give me error , any idea ?
dateDiff(truncDate(“WK”, truncDate(‘YYYY’, {search_date})), {search_date}}, ‘WK’)+1

2

Hi @MajidFa,
It looks like you may be receiving the error code from trying to use truncdate for the week after already using for the year.

If looking to get the week number (based on beginning of year) you can use the following steps:
Start by making your date field the first day of the year, dynamic to each year -

`addDateTime(-(extract(‘DD’, {Order Date}) - 1), ‘DD’, addDateTime(-(extract(‘MM’, {Order Date})-1), ‘MM’, {Order Date}))

Then you can nest this timestamp into a dateDiff to receive the difference in weeks between the first week of the year and the one in your field - \

dateDiff(addDateTime(-(extract('DD', {Order Date}) - 1), 'DD', addDateTime(-(extract('MM', {Order Date})-1), 'MM', {Order Date})), {Order Date}, 'WK')

Let me know if this works for what you’re looking for or if you have any further questions.

Thank you!

3

Hi @MajidFa ,

Please check the curly braces, there are 2 braces after search_date. I tried recreating the same formula and it worked fine.

Sample formula I recreated :
dateDiff(truncDate(“WK”, truncDate(‘YYYY’, {orderdate})), {orderdate}, ‘WK’)+1

Thanks,
Prantika

4

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!