12 Replies Latest reply on Dec 13, 2018 12:15 AM by Chris McClellan

    Group dates into 4 week periods

    L L

      I am trying to group dates into 4 week periods, I have found this post by Mark Bi-weekly data grouping  and it works perfect for two weeks. Can anyone figure out how to modify this calculation to group into 4 weeks instead of 2 weeks?

       

      This is Marks original Calculation:

       

      IF DATEDIFF('week',DATETRUNC('week',[Date Field]),#7/27/2014#)%2=0 THEN

          DATEADD('week',2,DATETRUNC('week',[Date Field]))-1

      ELSE

          DATEADD('week',1,DATETRUNC('week',[Date Field]))-1

      END

       

        • 1. Re: Group dates into 4 week periods
          Jim Dehner

          Hi LL

          the formula you posted uses a function called Modulo - it is the expression %2  

          what it does is divide the expression by 2 and return the remainder - if it is 0 then it is true and executes the then clause

           

          in your case substitute     %4=0

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Group dates into 4 week periods
            L L

            Hey Jim,

            that does not return the desired results, please see example that is attached. That was my first thought also but it is doing something funky when you change that to 4.

            • 3. Re: Group dates into 4 week periods
              Jim Dehner

              it the formula in your post worked on a 2 week cycle

               

              this will work on 4

               

               

              IF DATEDIFF('week',DATETRUNC('week',[Date Field]),#7/27/2014#)%4=0 THEN

                  DATEADD('week',2,DATETRUNC('week',[Date Field]))-1

              ELSE

                  DATEADD('week',1,DATETRUNC('week',[Date Field]))-1

              END

               

              it it did not work so I can see what is going on

              JIm

              1 of 1 people found this helpful
              • 4. Re: Group dates into 4 week periods
                Michel Caissie

                You have to take into account the  4 possible returned values of the  modulo 4.

                Try this

                 

                IF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=0 THEN

                    DATETRUNC('week',[Order Date])

                ELSEIF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=-1 THEN

                    DATEADD('week',-1,DATETRUNC('week',[Order Date]))

                ELSEIF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=-2 THEN

                    DATEADD('week',-2,DATETRUNC('week',[Order Date]))

                ELSEIF DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4=-3 THEN

                    DATEADD('week',-3,DATETRUNC('week',[Order Date]))

                END

                 

                Michel

                3 of 3 people found this helpful
                • 5. Re: Group dates into 4 week periods
                  L L

                  This is the correct answer, one more thing how would you modify this so that the max date is always the end of the most recent period. in my data set the date is the week ending date so only one date per week.

                  so if I have:

                  Time Period End Date

                  10/7/2018

                  9/30/2018

                  9/23/2018

                  9/16/2018

                  9/9/2018

                  9/2/2018

                  8/26/2018

                  8/19/2018

                   

                  this would be the desired result:

                   

                  4 week period
                  Time Period End Date
                  10/7/2018

                  10/7/2018

                  9/30/2018

                  9/23/2018
                  9/16/2018
                  9/9/20189/9/2018
                  9/2/2018
                  8/26/2018
                  8/19/2018
                  • 6. Re: Group dates into 4 week periods
                    Chris McClellan

                    This should work:

                     

                    case DATEDIFF('week',DATETRUNC('week',[Order Date]),#12/29/2013#)%4
                    when 0 then DATEADD('week',1,DATETRUNC('week',[Order Date]))
                    when -1 then DATEADD('week',0,DATETRUNC('week',[Order Date]))
                    when -2 then DATEADD('week',3,DATETRUNC('week',[Order Date]))
                    when -3 then DATEADD('week',2,DATETRUNC('week',[Order Date]))
                    END
                    

                     

                     

                    But Michel Caissie deserves the correct answer on this one, I just rewrote it to be a bit easier to read and changed the dates to match your request.

                    1 of 1 people found this helpful
                    • 7. Re: Group dates into 4 week periods
                      L L

                      This is much cleaner but doesn't address making the time periods align with the max date in the dataset. you can think of it as a rolling time period, so the max date is the is the most recent period and include the previous 4 weeks and so on.

                      • 8. Re: Group dates into 4 week periods
                        Chris McClellan

                        OK, I don't understand exactly what you mean - can you create some dummy data in CSV or XLS to explain ?  Even if it's just the time dimension and what you expect .....

                         

                        OR ... do you mean that the 4 week period allocation will change every day when a new day is added ?

                        • 9. Re: Group dates into 4 week periods
                          L L

                          Ok best way to think of this is that the start of the 4wk time period is the max(date) in the dataset and the other members of that 4wk time period are WEEK(MAX(DATE))-4.

                          In my dataset the date field is the week ending date so there is only one date per week.

                          Example:

                          10/7/2018

                          9/30/2018

                          9/23/2018

                          9/16/2018

                          9/9/2018

                          9/2/2018

                          8/26/2018

                          8/19/2018

                           

                          So in this example the first time period would be 10/7/2018 and it would include 10/7/2018, 9/30/2018, 9/23/2018, 9/16/2018 then the 4wk periods go on from there. So the next period would be 9/9/2018 and it would include 9/9/2018, 9/2/2018, 8/26/2018, 8/19/2018 and so on. But it will be rolling so when a new date is added that will be the start to the 4wk periods.

                          • 10. Re: Group dates into 4 week periods
                            Chris McClellan

                            OK, I think this is what you're after

                             

                            2018-12-13 16_50_24-Tableau - 856173.png

                            1 of 1 people found this helpful
                            • 11. Re: Group dates into 4 week periods
                              L L

                              Bingo that work... thank you so much!

                              • 12. Re: Group dates into 4 week periods
                                Chris McClellan

                                Awesome, happy to help