2 Replies Latest reply on Aug 31, 2016 10:58 PM by Lance Martens

    Assistance required with configuring a Customised “Date” Calculated Field

    Lance Martens

      Hi All,

       

      I would appreciate any assistance with how to configure a Customised “Date” Calculated Field?

       

      Logic:

      • I have unique “Reporting Weeks”

       

      Required:

      • I am attempting to create a calculated field that will give me unique “Reporting Months” based on the grouping of the Reporting Weeks

       

      • Calculation / Formula example of what I’m aiming for and I need help with please?

      IF [Reporting Week] = 1, 2, 3, 4, 5 then "January"

      ELSEIF [Reporting Week] = 6, 7, 8, 9 then "February".........etc. all the way to December

       

      My workbook is attached.

       

      Any assistance will be appreciated,

       

      Cheers,

      Lance

        • 1. Re: Assistance required with configuring a Customised “Date” Calculated Field
          Ashish Chaudhari

          Hi Lance,

           

          Please take a look at the solution. I don't think you need any calculation to fix this up. You can do it with the tableau date options which you should be using on the Calendar Date.

           

          Please find the below attached screenshot to verify the same. Also find the attached tableau 10 workbook for the same for reference.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Assistance required with configuring a Customised “Date” Calculated Field
            Lance Martens

            Hi Ashish,

             

            Appreciate the feedback mate!

             

            To explain my problem we will only evaluate the data for 2016 for now.

             

            • I agree that if we compare the “Reporting Week” Dimension with the “Calendar Date Reporting Week” the production data is the same for each week.

             

            • However, if we introduce a monthly component  based on the number of days in that month like in worksheet "Calendar Date Reporting Month" you can clearly observe how the “Calendar Date Reporting Week” for some months cross over between different months which it should not be doing.

             

            • You can also observe the problem in Worksheet “Sheet 2 & 6” for February where Week 10 falls in February and March, there are also clashes in other months of the year

             

            • The table below is a further illustration of how my 2016 weeks are grouped into the respective months:

             

             

            Month

            Weeks

            January

            1,2,3,4,5

            February

            6,7,8,9

            March

            10,11,12,13

            April

            14,15,16,17,18

            May

            19,20,21,22

            June

            23,24,25,26

            July

            27,28,29,30,31

            August

            32,33,34,35

            September

            36,37,38,39

            October

            40,41,42,43,44

            November

            45,46,47,48

            December

            49,50,51,52,53

             

            Required:

            • I need a calculated field that will group the respective Weeks together to give me the twelve months

             

            • Typically, a formula that will allow me to add, Week 1 to 5 to give me “January” and so on for all the other months of the year

             

            • My workbook is attached, I have also attached the 2016 reporting weeks and months for context on what I am trying to replicate (see attached pdf)

             

             

            Thanx again for your support Ashish!

             

            Cheers,

            Lance