10 Replies Latest reply on May 10, 2012 11:48 AM by Sorbsbuster

    Calculating Week of Quarter

    mgxdigital

      Title

      Calculating Week of Quarter

      Post

      Hello,

      Is there a way to calculate what week of the quarter the current date is in?

       

      I know how to calculate which quarter the current date is in, but i need it more narrowed down somehow.

        • 1. Re: Calculating Week of Quarter
          Sorbsbuster

          If you knew the start date of the quarter, could you not work out its week number, then subtract it from the current date's week number?  or, if you know the quarter the current date is in, then subtract 13, 26, or 39 from it, as appropriate?

          • 2. Re: Calculating Week of Quarter
            mgxdigital

            I do not know the start date of hte quarter.

            The only thing that I know is how to get the quarter itself.

            Celing ( Month ( Get ( CurrentDate ) ) / 3 )

            Which returnes 1, 2, 3, or 4 for the quarter.

            So as of today, we are in quarter 2.

            I'm just trying to figure out how to calculated what week of quarter 2 we are in.

            • 3. Re: Calculating Week of Quarter
              philmodjunk

              Date for Q1: Date ( 1 ; 1 ; Year ( get ( currendate ) ) )

              For Q2, its: Date ( 4 ; 1 ; Year ( get ( currendate ) ) )

              and so forth.

              You can use this inside a choose function:

              WeekOfYear ( Yourdate ) - WeekOfYear ( choose ( Quarter ; "" ; Date ( 1 ; 1 ; Year ( get ( currendate ) ) ) ; Date ( 4 ; 1 ; Year ( get ( currendate ) ) ) ; //and so forth ) ) + 1

              Should tell you what week of the quarter you are currently in but if the start of the quarter falls on a Saturday, week 1 of your quarter will be a single day....

              • 4. Re: Calculating Week of Quarter
                mgxdigital

                Thanks Phil,

                What is the "quarter" in your calcuation ((choose ( Quarter ; "" )?

                • 5. Re: Calculating Week of Quarter
                  philmodjunk

                  Ceiling ( Month ( Get ( CurrentDate ) ) / 3 )

                  I've assumed that you already have a field where this value is calculated.

                  • 6. Re: Calculating Week of Quarter
                    mgxdigital

                    Thank you again.

                    Can you please check this and tell me what i'm doing wrong. My calculation is currently reading out 20. Which i'm assuming is 19th week of the year plus 1.

                     

                    WeekOfYear ( d_Current Date ) - WeekOfYear ( Choose ( d_Current Quarter ; "" ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ; Date ( 4 ; 1 ; Year ( Get (CurrentDate ) ) ) ;  Date ( 7 ; 1 ; Year ( Get (CurrentDate ) ) ) ; Date ( 10 ; 1 ; Year ( Get ( CurrentDate ) ) ) ) )  + 1

                     

                    My calculations d_Current Date is correct and produces the current date. And d_Current Quarter is correct as well which produces 1, 2, 3 or 4 based on which quarter the current date is in.

                    • 7. Re: Calculating Week of Quarter
                      philmodjunk

                      I copied your calculation pasted it into Advanced's data viewer and then put it inside a Let function to assign values to d_Current Date and d_current Quarter:

                      Let ( [ d_Current Date = Get ( CurrentDate ) ;
                                 d_Current Quarter = Ceiling ( Month ( d_Current Date ) / 3 )
                              ];
                                 WeekOfYear ( d_Current Date ) -
                                 WeekOfYear ( Choose ( d_Current Quarter ; "" ;
                                                                                                         Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ;
                                                                                                         Date ( 4 ; 1 ; Year ( Get (CurrentDate ) ) ) ;  
                                                                                                         Date ( 7 ; 1 ; Year ( Get (CurrentDate ) ) ) ;
                                                                                                         Date ( 10 ; 1 ; Year ( Get ( CurrentDate ) ) ) ) )  
                                  + 1 )

                      And I get 6 as a result.  (19 - 14 + 1 )

                      check d_Current Date to make sure that the value is of type date. You might also modify your expression to use Get ( CurrentDate ) in place of d_current date as a test.

                      • 8. Re: Calculating Week of Quarter
                        Sorbsbuster

                        I thought

                        WeekOfYear ( DateField ) -

                        Case (

                        WeekOfYear ( DateField ) > 39 ; 39 ;

                        WeekOfYear ( DateField ) > 26 ; 26 ;

                        WeekOfYear ( DateField ) > 13 ; 13 ;

                        0

                        )

                        had a nice simplicity about it.

                        • 9. Re: Calculating Week of Quarter
                          philmodjunk

                          That assumes that every quarter starts on a Sunday...

                          • 10. Re: Calculating Week of Quarter
                            Sorbsbuster

                            I thought it was a reasonable starting point.  I originally had WeekOfYearFiscal, then I replaced it for simplicity's sake, in case the idea wasn't of any interest.