5 Replies Latest reply on Oct 26, 2016 6:53 AM by David Li

# Finding Sunday's date using year/week number only

Hi,

I am assuming I need to use a mixture of DATEADD/DATEPART but not sure.

So for example, I have a year column and week number column.  If year is 2016 and week number is 44, I need to calculate Sunday's date of that week.

So taking today 25th October, the YEAR is 2016, WEEK NUMBER is 44, so the result should be Sunday's date... 30th October.

• ###### 1. Re: Finding Sunday's date using year/week number only

Hi Paolo, try something like this:

DATEADD('day', ([Week Number]) * 7, DATETRUNC('week', DATE(STR([Year]) + "-01-01")))

Basically, what we're doing is finding the Sunday on or before January 1 of the year in question, then adding a number of days equal to Week # x 7 to it.

EDIT: Tested and fixed.

2 of 2 people found this helpful
• ###### 2. Re: Finding Sunday's date using year/week number only

Hi David,

Many thanks.  This works very well!

May I please ask one other question regarding LookUp (Excel's VLOOKUP equivalent), which you may/may not be able to help me with.

Example table:

Week - Invoice No

34          123

35          456

36          789

So if I want to lookup week 35 in the above table and return the value of 456, how can I do this?

• ###### 3. Re: Finding Sunday's date using year/week number only

You're welcome! And this is more complicated to do in Tableau because LOOKUP() doesn't work like VLOOKUP(). In Excel, you can refer to any cell from any other cell, but in Tableau, you can't really reference other records in the data set. However, you could potentially use level-of-detail (LOD) calculations to get around this, albeit somewhat clumsily.

{ MAX(IIF(Week=35, [Invoice No], 0)) }

Note that this only works for a single value, though. You can replace 35 with a parameter, but not really a dimension (to do this across multiple weeks).

However, if you're trying to do a lookup, you might be able to use a data blend to get the desired result.

1 of 1 people found this helpful
• ###### 4. Re: Finding Sunday's date using year/week number only

Thank you very much for your help David.  Much appreciated.

• ###### 5. Re: Finding Sunday's date using year/week number only

My pleasure!