Dates; changing months into quarters

[SouthsideFinance]

how can I create quarters using a field called Booking Month or Booking Year

[PhanThanhSon]

Hi,

try this:

'Q' & ceil(month(bookingmonth)/3) as quarter

best regards Son

[SouthsideFinance]

thanks ... i had trouble with that method but i was able to do it with the following:  thanks for the help!

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

];

[PhanThanhSon]

Good!

this maybe Happen because you Month Field isnt cast as date Field. 

[stevedark]

Hi @SouthsideFinance 

The problem you will have with this approach is that Month is a string, and therefore will not sort correctly.

If the only date you have in your data is a string based month then you can convert that to a date, and then use that date in a preceding load.

MonthQuarter:
LOAD
   *,
   'Q' & ceil(Month/3) as Quarter,
   ceil(Month/3) as QuarterNo
   ;
LOAD
   Month as MonthTxt,
   Month(Date#('01 ' & Month & ' 1999', 'DD MMM YYYY')) as Month
INLINE [
Month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
];

What the code is doing here is building a full date, including the month, and then converting that to a numeric date. Once you have a numeric date you can get the month that this comes from as a dual data type (i.e. Jan=1, Feb=2 etc.). Without doing that the month is just a piece of text and you can't sort or do other functions over it. The code above gives both a Month and a MonthTxt field and you will see how they behave here:

Hope that makes sense?

Steve

[rwunderlich]

@stevedark I don't think you have to do the full fake date any more. You can just do 

Month(Date#(Month  'MMM')) as Month

-Rob

[stevedark]

Hi @rwunderlich 

Hope you are keeping well!

I did wonder that, I thought I would go with what I knew would definitely work. 

Having a quick test now, it gives you the first of that month in 1899 - obviously:

Thanks for pointing this out Rob, every day's a school day!

Steve