Calculating quarters

[vijetas42]

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

[MK_QSL]

Check enclosed file...

[tresesco · Accepted solution]

Try like:

Load  *,

          '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

          *;

Load Year(Date)                              as Year,           // Your standard master calendar

         Month(Date)                            as Month

From <>;

[vijetas42]

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

[#5]

Try this:

Load *,

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

Cheers,

Lucas

[MK_QSL]

Sales:

Load * Inline

[

  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:

Load

  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:

Load

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

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

InvoiceMasterCalendar:

LOAD

  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;

[er_mohit]

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,