topic Quarter & Trimester breakdown in QlikView

Quarter & Trimester breakdown

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Quarter & Trimester breakdown

Gysbert_Wassenaar — Wed, 23 Mar 2016 13:26:06 GMT

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

Re: Quarter & Trimester breakdown

shamitshah — Tue, 29 Mar 2016 08:40:17 GMT

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