5 Replies Latest reply on Oct 19, 2016 4:31 AM by Jason Cheung

    how to calculate week of the quarter

    Jesse yung

      The default wknum seeks to be week number from the start of the year and runs 1 to 53 or 54.

      If I need to show week number within the quarter, running 1 to 13 or 14 within each qtr, how should I do so? Thanks!

        • 1. Re: how to calculate week of the quarter
          Sankarmagesh Rajan

          Hi Jesse,

           

          drag the quarter into row and then drag week num into row.

           

          Now we can get the quarter based weeks only.

           

          Thanks

          sankar

          • 2. Re: how to calculate week of the quarter
            Tableau kumar

            Hi  Jesse yung ,

             

            1) Create a calculation like below

            Name: Week Number

            Syntax: datepart('week', [Date_field])

            In the above  calculation "Datepart" is one of the Date Calculation Functions.

            so we get the output like  1,2,3,---------------------52 ,53

            2) Then add another calculation

            Name: Week of the Quarter

            Syntax: [Week Number]%13

            In the above  calculation "%" is the Modulo Function

             

            3) Then drg the Year, Quarter, Week Number, Week of the Quarter

            4) Now we get your expected result.

            ================================

            We have an alternative approach is using Index() function & adding the Addressing , Partitions accordingly.

            So Numbers re starts from every Quarter

             

            I hope it will help you.

             

            Best Regards

            Laxman Kumar

            • 3. Re: how to calculate week of the quarter
              Tableau kumar

              1) Create a calculation to get the Week Numbers,

              3.png

              2) Create another calculation to get the serial No,

              4.png

              3) Add the Index() to the work area, then convert to Discrete.

              5.png

              4) Right click on "Index()", then define the Addressing, Partitions accordingly.

              6.png

              Now we get the Required Week Numbers, Week numbers are restarting at Quarter Level.

              7.png

               

              I hope it will help you.

               

              Best Regards

              Laxman Kumar

              • 4. Re: how to calculate week of the quarter
                Jason Cheung

                Hi Laxman

                 

                Laxman Kumar B   Can you help me with my calculation?   I followed the instruction you gave but the results aren't exactly what I was looking for but ITS REALLY CLOSE!!!

                 

                As you can see in the screenshot below, the calculation you suggested (bottom row) is fine until it gets to the end of the quarter. I want the week in 1-14 regardless of which quarter we are in.   Each quarter has 14 weeks, but the calculation you suggested stops at week 12 and starts again at 0 when it should be week 13. 

                 

                I don't know how to adjust the formula to get that change.  I can't use the index() option since my data has gaps.  If I use index(), it may show as week 1 when it should be week 2 because week 1 has no data. 

                 

                Much appreciated!!!!

                 

                Best,

                Jason

                • 5. Re: how to calculate week of the quarter
                  Jason Cheung

                  Hi Jesse yung,

                   

                  I found a solution that works for me. I got the idea from here: excel function for week as per defined quarter - Microsoft Community

                   

                  Logic to find the Week of Quarter:

                  1 + (WeekNUM of date - WeekNUM of 1st day of Quarter)

                  Find the following:

                  1. Weeknum of current date: DATEPART('week',[Creation Date])

                   

                  2.Weeknum of 1st day of Quarter: DATEPART('week',DATETRUNC('quarter',[Creation Date]))

                   

                  Week of Quarter:

                  1+(DATEPART('week',[Creation Date])-DATEPART('week',DATETRUNC('quarter',[Creation Date])))

                   

                  Thanks PinakiBrahma for the idea!

                  1 of 1 people found this helpful