Converting month numbers to Quarters

[Anonymous]

In my table I have a Column called ACCOUNTING_PERIOD and its values are 1-12 representing fiscal months. What is the easiest way to represent this column as a quarters?

Currently I am (trying to) using a list box and for field I am using expressions, and editing it little by little to see if I get it figured out.. However I have not been completely successful in doing this. The expressions I have come up with are based on what I have found while using google, and they are:

If(Month(QuarterStart(OrderDate))='1',4,Div(Month(QuarterStart(OrderDate)),3))

and

if(left(QuarterName(TempDate),7)='Jan-Mar','Q1',

               if(left(QuarterName(TempDate),7)='Apr-Jun','Q2',

               if(left(QuarterName(TempDate),7)='Jul-Sep','Q3','Q4'))) 

I haven't had much luck when I have been adjusting these as expressions. So does anyone know of a simple expression that can help me achieve exactly what I am trying?

Thank you

[shree909 · Accepted solution]

Hi Take an  inline Table:

Load * inline[

Month_ID, Month, Quarater

1,Jan,Q1

2,Feb,Q1

3,Mar,Q1

4,Apr,Q2

5,May,Q2

6,Jun,Q2

7,Jul,Q3

8,Aug,Q3

9,Sep,Q3

10,Oct,Q4

11,Nov,Q4

12,Dec,Q4.

];

anyway u have monthid 's try to join on this fiedl, u will get month and Quarter,

Hope this helps

Thanks

[alexandros17]

If your field is a date for example myDate:

If(month(myDate)<=3,'Q1',

     if(month(myDate)>=4 and month(myDate)<=6,'Q2',

          if(month(myDate)>=7 and month(myDate)<=9, 'Q3',

               'Q4'

          )

     )

)

[aveeeeeee7en]

Hi Joe

See the Attachment.

Its Simple and easy.

Use this Code in your script and do replace the Date with your Date:

'Q'&Ceil(Num(Month(AddMonths(Date,0)))/3)

as [Fiscal Quarter]

Regards

Aviral Nag

[javier_florian]

Hi Joe,

Can you try with this:

Year(Date_Tmp')&'_'&Ceil(Month(Date_Tmp')/3)&'Q'

-JFlorian

[Anonymous]

I marked the one that was to me the easiest with out explanation to figure out. It may not be in an expression form, but I was able to get the results I wanted.

Thank you to all who helped

[maxgro]

='Q' & ceil(num(month(floor(QuarterName(REPLACEWITHYOURDATE))))/3)

[SouthsideFinance]

I was able to accomplish this with the following code tweak... thanks !

LOAD * INLINE [

Booking Month, Booking Quarter

Jan, Q1

Feb, Q1

Mar, Q1

Apr, Q2

May, Q2

Jun, Q2

Jul, Q3

Aug, Q3

Sep, Q3

Oct, Q4

Nov, Q4

Dec, Q4

];