Financial Quarter

[joeybird]

I have the following calendar code

Calendar: DECLARE FIELD DEFINITION TAGGED '$date' Parameters first_month_of_year = 1 Fields

     Year($1) As Year Tagged '$year',
    
     if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear', 

     Month($1) as Month Tagged '$month',
    
     if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',

     Date($1) as Date Tagged ('$date', '$day'),

     Week($1) as Week Tagged '$week',

     Weekday($1) as Weekday Tagged '$weekday',
    
     WeekEnd ($1) as WeekEnd Tagged '$weekend',
    
     'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter',
    
   
    

     DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS [EventDate] USING Calendar;

but I have an issue where Q1 starts Jan, Feb, March, BUT I need Apr, May, June as Q1

Please help

[rubenmarin]

Hi Joanna, you can use the same logic as previous rows to calculate the quarter:

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/3)  as FinancialQuarter Tagged '$FinancialQuarter',

Or maybe:

'Q' & Ceil(Month(AddMonths($1, -3))/3)  as FinancialQuarter Tagged '$FinancialQuarter',

[joeybird]

Hiya

I keep getting the same, but little different

Q2 keeps coming up as Oct, Sept, Dec, Aug

please help

[jonathandienst · Accepted solution]

Shouldn't that be divided by 4?

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/4)

[rubenmarin]

Hi Joanna, I tried with standard Master calendar and seems to work:

Not sure wich value has your $1, PFA to check differences.

[rubenmarin]

That happens to me sometimes, in spanish the word 'Trimestre' refers to a 3-month block but in english 'Quarter' seems related to the year spliteed in 4 parts, leading to think in that '4' to divide, but the 12 months has to be divided by 3 to get the 4 parts.

[joeybird]

Hiya

thank you

cracked it, with your help too

'Q' & Ceil(if( Month($1)>=4, Month($1)-3,Month($1)+9)/3)as FinancialQuarter Tagged '$FinancialQuarter';

Kind Regards