Skip to main content
Topics#Tableau Desktop & Web AuthoringTableau Community Forums Member (Inactive)'s Question
Tableau Community Forums Member (Inactive) (Salesforce) asked in #Tableau Desktop & Web Authoring
Oct 25, 2016, 9:51 AM

First day of the week of my past full week

Hello everyone,

 

I have been looking for a solution that I really cannot find.

 

I have a previous full week number formula that works fine:

[WEEK NUMBER]=datepart('week',dateadd('week',-1,Today()))

 

Than I do:

DATEADD('day',1,date([WEEK NUMBER]))

 

Which gives me 14/02/1900 00:00:00

BUT I WANT: 16/10/2016

 

Really I am far...

I have understood why I think; which is because [WEEK NUMBER] is not for this years previous week at present.

 

Any tips?

 

Thanks a lot

 

Regards

2 answers
  1. Simon Runc (Atheon Analytics)
    Oct 25, 2016, 11:09 AM

    hi Leslie,

     

    So the problem here is that your [Week Number] is the datepart of week...which is just the week number (say 44, or Week 44). When you then try and use this to create a date it only has the number 42! Dates are stored as numbers, which are then converted to the dates we (as humans) understand...starting from 01/01/1900....so 1st Jan 1900 is the number 1, 2nd Jan the number 2...and so on.

     

    So if your [Week Number] was 44...the calculation you have says; convert the number 44 to a date...which would be 13/02/1900 (day 44 from 1900), and then add one to it (to give the 14/02/1900).

     

    Something like

     

    DATEADD('day',1,dateadd('week',-1,Today()))

     

    would give you a date from the previous week....you may need to substitute the -1, with a calculation on DATEPART('weekday',today()). This returns the weekday number (so Monday = 2, Tuesday = 3....) meaning you can adjust which date is returned depending on which day of the week it is (if for example, you always want to show the date as a weekending value).

     

    Hope that helps and makes sense.

Write an answer...
0/9000