topic Quarter calculation in App Development

Quarter calculation

paulwalker — Tue, 21 May 2024 04:34:09 GMT

Hi Experts,

I have to create quarter functionality

Q1 = Jan24, Feb24, Mar24

Q2 = Apr24, May24, June24

Q3 = Jul23, Aug23, Sep23 (If we enter to July Q3 should be Jul24 and Q4 remains same)

Q4 = Oct23, Nov23, dec23

I'm trying with code,

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -2)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

DATE_CALENDAR:
LOAD
CALENDAR_DATE AS %KEY_CALENDAR_DATE,
DATE(CALENDAR_DATE) AS CALENDAR_DATE,
YEAR(CALENDAR_DATE) AS CALENDAR_YEAR,
DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS CALENDAR_MONTH,
NUM(MONTH(CALENDAR_DATE)) AS CALENDAR_MONTH_NUM,
NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS CALENDAR_YEAR_MONTH_NUM,
DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS CALENDAR_WEEK_ORDER,
DAY(CALENDAR_DATE) AS CALENDAR_DAY,
DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS CALENDAR_WEEK_DAY,
'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS CALENDAR_QUARTER,
DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
YEAR(WEEKSTART(CALENDAR_DATE)) & NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00')) AS CALENDAR_WEEK;
LOAD
($(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

SET S_DATE_START=;
SET S_DATE_END=;

EXIT SCRIPT;

Thanks in Advance!

Re: Quarter calculation

ali_hijazi — Tue, 21 May 2024 05:53:50 GMT

Hello
this is how I create the Quarter field
I first create a temp calendar table starting from a desired year till today ($(START_YEAR))

let StartDate = num(DayStart(YearStart(MakeDate(2015)))); /*STARTING FROM 2015 for example*/

let EndDate = num(daystart(monthend(Today())));

//Create a temporary calendar

TempCalendar:

load recno() as Date_Key,

'$(StartDate)'+recno()-1 as PeriodDate

autogenerate(EndDate-StartDate+1);

MasterCalendar:

load

num(daystart((Date(PeriodDate)))) as DATE,

Date(PeriodDate) as PERIOD_DATE,

Day(PeriodDate) as DAY,

Year(PeriodDate) as YEAR,

Month(PeriodDate) as MONTH,

Num(Month(PeriodDate)) as MONTH_NUM,

'Q' & Ceil(Month(PeriodDate)/3) as QUARTER,

MONTHNAME(PeriodDate) as MONTH_YEAR,

'Week '&Ceil(Day(PeriodDate)/7) AS WEEK_NUMBER,

WeekDay(PeriodDate) as WEEK_DAY,

inyeartodate(num(PeriodDate), num(Daystart(Today())), Year(Date(PeriodDate)) - Year(Today())) * -1 AS Date_is_YTD

resident TempCalendar order by PeriodDate Asc;

Drop table TempCalendar;

Re: Quarter calculation

TauseefKhan — Tue, 21 May 2024 06:02:04 GMT

Hi @paulwalker,
Replace with this you Quarter Condition:

'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS CALENDAR_QUARTER,

Use This One:
IF(NUM(MONTH(CALENDAR_DATE)) >= 7, 'Q' & CEIL((NUM(MONTH(CALENDAR_DATE)) - 6)/3), 'Q' & CEIL((NUM(MONTH(CALENDAR_DATE)) + 6)/3)) AS CALENDAR_QUARTER,

*** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. ***