topic Re: Financial Quarter in App Development

Financial Quarter

joeybird — Mon, 14 Dec 2015 12:35:01 GMT

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

Re: Financial Quarter

rubenmarin — Mon, 14 Dec 2015 12:51:54 GMT

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',

Re: Financial Quarter

joeybird — Mon, 14 Dec 2015 13:22:17 GMT

Hiya

I keep getting the same, but little different

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

please help

Re: Financial Quarter

jonathandienst — Mon, 14 Dec 2015 13:36:09 GMT

Shouldn't that be divided by 4?

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

Re: Financial Quarter

rubenmarin — Mon, 14 Dec 2015 13:42:41 GMT

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

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

Re: Financial Quarter

rubenmarin — Mon, 14 Dec 2015 13:51:12 GMT

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.

Re: Financial Quarter

joeybird — Mon, 14 Dec 2015 14:02:18 GMT

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