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

    Dates into Week Number

    Kevin Keoghan

      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)