6 Replies Latest reply: Mar 12, 2014 7:19 AM by Mohit Sharma

# Calculating quarters

Hi,

How should i calculate quartes from given date.I have tried but it's giving me quarter for each month but I want quarters according to fiscal month

• ###### Re: Calculating quarters

Check enclosed file...

• ###### Re: Calculating quarters

Can you just share script as I am unable to open your qvw?

• ###### Re: Calculating quarters

Sales:

[

InvoiceDate,Sales

01/01/2013, 100

20/01/2013, 200

01/02/2013, 300

28/03/2013, 400

03/04/2013, 150

04/05/2013, 250

05/05/2013, 300

06/06/2013, 100

06/07/2013, 120

07/07/2013, 180

08/08/2013, 60

09/09/2013, 80

10/10/2013, 280

01/11/2013, 300

01/12/2013, 165

10/01/2014, 140

15/01/2014, 160

20/02/2014, 190

];

MinMaxDate:

Min(InvoiceDate) as MinDate,

Max(InvoiceDate) as MaxDate

Resident Sales;

Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

TempDate:

Date(\$(vMinDate) + RowNo() - 1) as InvoiceTempDate

AutoGenerate \$(vMaxDate) - \$(vMinDate)+1;

InvoiceMasterCalendar:

InvoiceTempDate as [InvoiceDate],

Day(InvoiceTempDate) as InvoiceDay,

WeekDay (InvoiceTempDate) as InvoiceWeekday,

Week(InvoiceTempDate) as InvoiceWeek,

Month(InvoiceTempDate) as InvoiceMonth,

Num(Month(InvoiceTempDate)) as InvoiceMonthNO,

Year(InvoiceTempDate) as InvoiceYear,

'Q' & ceil(month(InvoiceTempDate) / 3) as InvoiceQuarter,

Date (monthstart (InvoiceTempDate) , 'MMM YYYY') as InvoiceMonthYear,

if(num(month(InvoiceTempDate))<=3,Year(InvoiceTempDate)-1&'-'&right(Year(InvoiceTempDate),2),Year(InvoiceTempDate)&'-'&right(year(InvoiceTempDate)+1,2)) as FinancialYear,

if(num(month(InvoiceTempDate))<=3,Year(InvoiceTempDate),year(InvoiceTempDate)+1) as FinancialYear1,

if(num(month(InvoiceTempDate))>=1 and num(month(InvoiceTempDate))<=3,'Q4',

if(num(month(InvoiceTempDate))>=4 and num(month(InvoiceTempDate))<=6,'Q1',

if(num(month(InvoiceTempDate))>=7 and num(month(InvoiceTempDate))<=9,'Q2',

if(num(month(InvoiceTempDate))>=10 and num(month(InvoiceTempDate))<=12,'Q3')))) as FinancialQuarter

RESIDENT TempDate

ORDER BY InvoiceTempDate ASC;

DROP TABLE TempDate;

• ###### Re: Calculating quarters

Try like:

'Q' & Celi(fMonth/3) as FiscalQtr;

Load Year + If(Month>=\$(vFM), 1, 0) as fYear,           // Numeric fiscal year

Mod(Month-\$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

*;

Month(Date)                            as Month

From <>;

• ###### Re: Calculating quarters

Try this:

'Q' & Ceil(Month/3) as Quarter

Cheers,

Lucas

• ###### Re: Calculating quarters

Try this code in script here change TempDate with your Date

'Q'&If(Month(QuarterStart(TempDate,4))='1' ,4,Div(Month(QuarterStart(TempDate,4)),3)) as QuarterStartsapr,