topic Re: Converting month numbers to Quarters in QlikView

Converting month numbers to Quarters

Anonymous — Fri, 21 Mar 2014 19:55:18 GMT

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

Re: Converting month numbers to Quarters

shree909 — Fri, 21 Mar 2014 20:00:24 GMT

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

Re: Converting month numbers to Quarters

alexandros17 — Fri, 21 Mar 2014 20:05:11 GMT

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'

)

Re: Converting month numbers to Quarters

aveeeeeee7en — Fri, 21 Mar 2014 20:08:44 GMT

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

Re: Converting month numbers to Quarters

javier_florian — Fri, 21 Mar 2014 20:11:14 GMT

Hi Joe,

Can you try with this:

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

-JFlorian

Re: Converting month numbers to Quarters

Anonymous — Fri, 21 Mar 2014 20:19:41 GMT

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

Re: Converting month numbers to Quarters

maxgro — Fri, 21 Mar 2014 20:22:23 GMT

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

Re: Converting month numbers to Quarters

SouthsideFinance — Sat, 24 Feb 2024 14:48:38 GMT

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

];