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

    Calculating quarters

    vijeta sharma


      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
          Manish Kachhia

          Check enclosed file...

            • Re: Calculating quarters
              vijeta sharma

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

                • Re: Calculating quarters
                  Manish Kachhia

                  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;

              • Re: Calculating quarters
                Tresesco B

                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 <>;

                • Re: Calculating quarters

                  Try this:

                   

                  Load *,

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

                   

                  Cheers,

                  Lucas

                  • Re: Calculating quarters
                    Mohit Sharma

                    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,