2 Replies Latest reply on Jul 9, 2018 5:25 AM by Jim Dehner

# Dates into Week Number

Hi All

I'm wrestling with converting dates into weeknumbers

The first week of a year should normally be the first Monday of July as this coincides with the Financial Year.

I am using a Calculated Field (CF) "Dates Week" (See below) but it is not working correctly .

IF DATEPART("week",[Date visited])>27 THEN

'W'+if len(str(Datepart("week",[Date visited]) -27))=1

then

'0'+ str(Datepart("week",[Date visited]) -27)

else str(Datepart("week",[Date visited]) -27)

End

ELSE

'W'+if len(str(Datepart("week",[Date visited]) +27))=1

then

'0'+ str(Datepart("week",[Date visited]) +26)

else str(Datepart("week",[Date visited]) +26)

End

END

In my dummy data, in column D I have put what I think should be the week number based on my CF and in column E what Tableau is returning from my CF. Sometimes the CF gets it right but a lot of times it doesn't.

Hope this makes sense! (apologies for the appalling attachment)

• ###### 1. Re: Dates into Week Number
1 of 1 people found this helpful
• ###### 2. Re: Dates into Week Number

Good morning Kevin

the issue your are running into is week 53 (see the link  DATEPART Returns Week Value of 53 Instead of 1 | Tableau Software )

In tableau if the calendar year ends mid week that week is "week 53'

if it starts at the first day of the week it is week 1

That difference is throwing your calculation off

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful