4 Replies Latest reply on Sep 10, 2014 8:14 AM by basilisk2

    How do I calculate name of previous quarter?

    basilisk2

      Title

      How do I calculate name of previous quarter?

      Post

      I am using the Set Field script step to automatically set the date of one of two comparison dates. I can successfully set the current quarter [sjClientInfo::gQuarterTwo], but not the previous quarter [sjClientInfo::gQuarterTwo].

      The following code sets the field to the last last quarter of the last calendar year which is OK if the current quarter is Q1, but not for Q2, 3 or 4. How do I change this please?

      Set Field [sjClientInfo::gQuarterOne; Let ( T = Get ( CurrentDate ) ; If ( Ceiling ( Month ( T ) / 3 ) = 1 ; Year ( T ) -2 & " Q4" ; Year ( T ) - 1  & " Q4" ) )

      TIA!

        • 1. Re: How do I calculate name of previous quarter?
          raybaudi

          If I understand correctly:

          Set Field [ sjClientInfo::gQuarterOne ; Let ( T = Get ( CurrentDate ) ; If ( Month ( T ) > 3 ; "Q" & Ceiling ( Month ( T ) / 3 ) - 1 ; "Q4" ) ) ]

          • 2. Re: How do I calculate name of previous quarter?
            basilisk2

            I like your suggestion but I may not have explained very well, sorry.

            If the date the calculation is made is Jan, Feb or Mar 2014, I want gQuarterTwo to be set as 2013 Q4 (my script does that already so no change needed there) and I want gQuarterOne to be set as 2012 Q4.

            If the date the calculation is made is Apr, May or Jun 2014, I want gQuarterTwo to be set as 2014 Q1 (my script does that already so no change needed there) and I want gQuarterOne to be set as 2013 Q4.

            If the date the calculation is made is Jul, Aug or Sep 2014, I want gQuarterTwo to be set as 2014 Q2 (my script does that already so no change needed there) and I want gQuarterOne to be set as 2014 Q1.

            If the date the calculation is made is Oct, Nov or Dec 2014, I want gQuarterTwo to be set as 2014 Q3 (my script does that already so no change needed there) and I want gQuarterOne to be set as 2014 Q2.

            I then make a calculation for Value 'x' at the end of gQuarterTwo - Value 'x' at end of gQuarterOne to give me the change figure I am looking for for a list of records. Does that help? Sorry it's a bit wordy, it's my fifth attempt at explaining it concisely (I gave up on that).

            • 3. Re: How do I calculate name of previous quarter?
              raybaudi

              Set Field [ sjClientInfo::gQuarterOne ; Let ([ T = Get ( CurrentDate ) ; Y = Year ( T ) ] ; If ( Month ( T ) > 3 ; Y & " Q" & Ceiling ( Month ( T ) / 3 ) - 1 ; Y - 1 & " Q4" ) ) ]

              • 4. Re: How do I calculate name of previous quarter?
                basilisk2

                I'm glad you changed the instance of T to Y in the Let statement, that had me scratching my head initially! I changed it to Y and the four output tests I did manually all worked out logically, which was what I was really looking for. Now I have to implement it in the database itself.

                I also found it helped me understand what the code was doing by breaking it into multiple lines like so:

                Let 

                (

                    T = Get ( CurrentDate ) ; 

                    Y = Year ( T ) ; 

                    If ( 

                        Month ( T ) > 3 ; 

                        Y &" Q" & Ceiling ( Month ( T ) / 3 ) - 1 ; 

                        Y - 1 & " Q4" 

                    ) 

                )

                Thanks for your great help! smiley