4 Replies Latest reply on Sep 8, 2012 12:36 PM by Robert Bell

    Displaying week number AND month

    Robert Bell



      I'd like to show the month corresponding to the week number when displaying a year-on-year overlay (see attached PDF). I realise that the week no may 'belong' a different month in different years, but it's outputting a rough guide that I'm after. Since for this report the fiscal start date is set to May, the user will quite naturally need to see that the current week (19) is in August.


      Has anyone managed to achieve this?


      Many thanks



        • 1. Re: Displaying week number AND month
          Tracy Rodgers

          Hi Robert,


          Would creating a calculated field similar to the following and sorting it appropriately work?


          datename('month',[Order Date]) + ' Week ' + datename('week' ,[Order Date])



          1 of 1 people found this helpful
          • 2. Re: Displaying week number AND month
            Robert Bell

            Hi Tracy


            I've a created a calculated field as you suggest -


            'Week ' + datename('week',[Enrolment date]) + ', ' + datename('month' ,[Enrolment date])


            - and sorted it. Enrolment date has a fiscal start date of May, which doesn't seem to be reflected in the week numbers, e.g. I would expect the first in the sorted list to be Week 1, May, instead of Week 1, January.


            Any ideas?


            Thanks for all your help here



            • 3. Re: Displaying week number AND month
              Wilson Po

              Hi Bob,


              This is common as the fiscal start is much more of just changing the recognition of the dates rather than the dates themselves. When we run a formula on top of it, it tends to revert back to the Gregorian calendar convention rather than picking up our new fiscal start date. This is fine, since we can still mimic the fiscal date shift with a small change to formula:


              'Week ' + datename('week',dateadd('month',-4,[Enrolment date])) + ', ' + datename('month' ,[Enrolment date]) 


              Hope this helps!



              • 4. Re: Displaying week number AND month
                Robert Bell

                Hi Wilson


                Yes, that works!


                Thanks again ... where are you as a matter of interest? It's Saturday evening here in London - I hope you're not going to spend all day Tableau-ing too!