Quarter & Trimester breakdown

[shamitshah]

Hi,

I have the following in the script which is working fine for the Quarter's (the fiscal year is July - June):

'Q' & (mod(Ceil(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as Quarter,

    Dual('Q' & (mod(Ceil(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))/3)+1,4)+1) & '-' & Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))), Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) & (mod(Ceil(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))/3)+1,4)+1)) as QtrYear,

    if(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) > 6, Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))+1, Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))) as FiscalYear,

    dual(month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))),month(addmonths(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)),6))) as FiscalMonth,       

    Week(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as Week;  

I now require a trimester breakdown maintaining the Fiscal Year from July-June.

I replaced MakeDate(2000,1,1)))/3)+1,4)+1) with MakeDate(2000,1,1)))/4),4). It shows three quarters which is fine but the months are not aligned to the Fiscal Year (July - June).

Any suggestions?

Thanks

Shamit

[Gysbert_Wassenaar]

Dual('T'& Ceil(num(month(Monthstart([SAL_InvoiceDate],-6)))/4),Ceil(num(month(Monthstart([SAL_InvoiceDate],-6)))/4)

[shamitshah]

Hi Gysbert,

I made the change to the script (highlighted below). The script is now as follows:

Date([SAL_InvoiceDate] + MakeDate(2000,1,1)) as SaleDate,

Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as Year,

Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as Month,

Date(MonthStart(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))), 'MMMYY') as MonthYear,

    'T' & (Mod(Ceil(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))/4),4)) as TQuarter,

   Dual('T'& Ceil(num(month(Monthstart([SAL_InvoiceDate],-6)))/4),Ceil(num(month(Monthstart([SAL_InvoiceDate],-6)))/4)) as TQtrYear,

if(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) > 6, Year(Date([SAL_InvoiceDate] +    MakeDate(2000,1,1)))+1, Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))) as FiscalYear,

dual(month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))),month(addmonths(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)),6))) as FiscalMonth;

I have a list box field TQtrYear with the following result for Fiscal Year 2016:

T1: July 2015 - Oct 2015

T2:  Nov 2015 - Mar 2016 (picking up the 1 Mar 2016)

T3:  Apr 2016 - June 2016 (starts from 2 Mar 2016) 

T2 should be Nov 2015 -  Feb 2016. Any idea why T2 and T3 is out of sync?

Thanks

Shamit