topic Re: Dates; changing months into quarters in App Development

Dates; changing months into quarters

SouthsideFinance — Sat, 24 Feb 2024 13:33:39 GMT

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

Re: Dates; changing months into quarters

PhanThanhSon — Sat, 24 Feb 2024 14:47:50 GMT

Hi,

try this:

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

best regards Son

Re: Dates; changing months into quarters

SouthsideFinance — Sat, 24 Feb 2024 14:50:16 GMT

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

];

Re: Dates; changing months into quarters

PhanThanhSon — Sat, 24 Feb 2024 14:58:06 GMT

Good!

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

Re: Dates; changing months into quarters

stevedark — Sat, 24 Feb 2024 16:13:24 GMT

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

Re: Dates; changing months into quarters

rwunderlich — Mon, 26 Feb 2024 16:35:02 GMT

@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

Re: Dates; changing months into quarters

stevedark — Sun, 25 Feb 2024 16:10:27 GMT

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