Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Dual('T'& Ceil(num(month(Monthstart([SAL_InvoiceDate],-6)))/4),Ceil(num(month(Monthstart([SAL_InvoiceDate],-6)))/4)
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