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

# How do I calculate name of previous quarter?

### 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?

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?

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?

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?

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!