8 Replies Latest reply on Sep 17, 2013 12:59 PM by Wilson Becker

# Assigning a date range to week number?

Hello everybody- quick question! I'd be really happy if you can help me figure it out

My data source has four columns to identify time--> Year, Month, Week, and Date

Every row has a year, a month, and a week.. but only a couple rows have a date

Here's what my setup looks like so far:

What I'd like to do is assign a date range to each week, i.e. February Week 1 = 2/3/13 - 2/9/13, etc.

which was helpful, but not exactly what I'm looking for.

The only reason I need a date rage for each week is because only a few of my data rows have an exact date associated with them. Eventually, I'll want to roll those exact dates up into their respective weeks. I assume there will need to be some case statement using datediff() to determine which week an exact date belongs to.

Like I said, any help would be greatly appreciated! I'm still very new to Tableau

Thanks

• ###### 1. Re: Assigning a date range to week number?

Presumably this is all based off the start date that does not change?

• ###### 2. Re: Assigning a date range to week number?

Alex- thanks for clarifying. Exactly right, the start date is February 3 and does not change. I created a calculated field which is literally just #2/3/2013# because I expect that it will be used to create the week date range.

• ###### 3. Re: Assigning a date range to week number?

Okay, this is straight forward enough then. Give me a while. BTW, September is spelled wrongly - is it in the real data?

1 of 1 people found this helpful
• ###### 4. Re: Assigning a date range to week number?

Awesome thanks! And thanks for the heads up on 'Septebmer'. The real data is fine, I just created this mock up in a hurry

• ###### 5. Re: Assigning a date range to week number?

Okay a few calcs. I'm on 8, so you'll have to copy paste these. First a date conversion that gives you a real date for each year, month, week you have. It looks a little complex, but it allows the start date to change (please check it works with all scenarios):

Call this [Date]

date(

)

It is basically looking at the year, month, and week dimensions and comparing them with the start date. It is wrapped in date() to change it away from a datetime to just a date

Now we have this, the end of the week will be:

Call this [EOW]

[Date]+6

And then we can create our string to show the range

str([Date])+" to "+str([EOW])

• ###### 6. Re: Assigning a date range to week number?

That's awesome, makes perfect sense to me.

What is the calc for [Month Real]? I tried to call dateadd with (string, string, date), but that gave me an error. I converted [Month] to an integer, and now I've got null values for all calculations.

int([Month]):

date(

)

Thanks for dumbing it down for me. Very very helpful

• ###### 7. Re: Assigning a date range to week number?

Datepart('month',date("1/"+[Month]+"/2013"))

This could probably be simplified I would imagine

EDIT: Actually probably not significantly - this turns the month string into a real date

1 of 1 people found this helpful
• ###### 8. Re: Assigning a date range to week number?

Thanks, Alex! Really appreciate your help. As far as determining which week an exact date belongs to--

Let's say I have a row whose exact date is 4/4/2013. That falls into week 1 of April. The only way I can think of calculating it is to take the left and right of the Date Range String, covert them to datetime for upper and lower bounds, and do a datediff...

Thoughts?