10 Replies Latest reply: Nov 25, 2013 12:03 AM by Agnivesh Kumar RSS

    How to use Quater function

    Agnivesh Kumar

      How can i use Quater funtion if i have mnths in one column and year in other column.

        • Re: How to use Quater function


               You can add your quarter in a load script and  use as other columns.

               Write this in your load script.



                    'Q' & Ceil(Month(Date)/3) As Quarterly

              from tableName




          • Re: How to use Quater function
            Mohit Sharma

            Try this

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

            • Re: How to use Quater function
              Vishwaranjan Kumar


              try this



              //************************* Example one*******************************

              SET STARTDATE = ’01-01-2006′; //fill in your start date
              LET ENDDATE = date(today());
              date( date#(‘$(STARTDATE)’,'DD-MM-YYYY’)-1 + recno() ,’DD-MM-YYYY’) as DATE
              AUTOGENERATE (date#(‘$(ENDDATE)’) – date#(‘$(STARTDATE)’))+1;
              Year(DATE) as YEAR,
              Month(DATE) as MONTH,
              Week(DATE) as WEEK,
              WeekDay(DATE) as WEEKDAY,
              Day(DATE) as DAY,
              Year(DATE) & right(’00′ & week(DATE),2) as YYYYWW,
              Year(DATE) & right(’00′ & num(Month(DATE)),2) as YYYYMM,
              Year(DATE) & ceil(num(Month(DATE))/3) as YYYYK,
              ‘Quarter’ & ceil(num(Month(DATE))/3) as QUARTER
              RESIDENT [TEMP_DATE];

              DROP TABLE [TEMP_DATE];


              //************************* Example two*******************************

              We need the highest and lowest date values from the fact table and place these values into variables that we use to create a Calender table. Search for the date field (in this example we will call the field FactDate) in the fact table. FactDate will be the connecting field to the Calendar table. Sort the fact table by typing the following at the end of the select statement before the semicolon:

              * ORDER BY FactDate ASC;
              * Your fact table should look something like this:

              //************************* Fact table*********************************

              LOAD FactID,
              FROM facts ORDER BY FactDate ASC;

              (Remember that you should only have one date / time field in the fact table, the master calendar table will contain all the date formats.)

              * Create a new tab in the script editor and call it Calendar.
              * Create a new variable for the first date by typing the following script statement:
              LET varMinDate = Num(Peek(‘FactDate’, 0, ‘Facts’));

              Where FactDate is the linking Date field, 0 is the first row of the table and Facts is the FactTable. When there is more then one fact table, for example Facts2, your statement should look like this:
              LET varMinDate = Num(rangemin(Peek(‘FactDate’, 0,’Facts’), Peek(‘FactDate’, 0,’Facts2′)));

              * Create a second variable for the last date by typing the following statement:
              LET varMaxDate = Num(Peek(‘FactDate’, -1, ‘Facts’));

              * Create a third variable for the last date by typing the following statement:
              LET vToday = num(today());

              * Create a new table by typing the following script into the script editor:

              //************************Temporary Calendar***************************

              //Create all days in the range from varMinDate to varMaxDate
              $(varMinDate)+Iterno()-1 AS Num,
              Date($(varMinDate)+Iterno()-1) AS TempDate
              AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);

              * Once we have created all the dates needed, we can start creating a Master Calendar table where we create all the date fields needed.

              //**************************Master Calendar******************************

              //Building the master calendar with most date dimensions
              load TempDate as FactDate,
              week(TempDate) as Week,
              Year(TempDate) as Year,
              Month(TempDate) as Month,
              Day(TempDate) as Day,
              Year2date(TempDate)*-1 as CurYTDFlag,
              Year2date(TempDate,-1)*-1 as LastYTDFlag,
              Date(monthstart(TempDate), ‘MMM-YYYY’) AS MonthYear,
              Week(TempDate)&’-'&Year(TempDate) as WeekYear,
              Weekday(TempDate) as WeekDay
              resident TempCalendar order by TempDate Asc;

              * Before we reload the script, we need to get rid of the TempCalendar table used for creating the dates:
              Drop Table TempCalendar;

              Save and reload your script

              • Re: How to use Quater function
                vishal waghole

                Hi Agnivesh,


                Please try this


                Load   MONTH,

                          'Q' & Ceil(Num(MONTH)/3) As Quarter

                from   TableName;


                hope it will work for u.




                Vishal Waghole

                • Re: How to use Quater function
                  Vinoth Kumar Rajendran

                  Hi Agnivesh,


                  1. You can create the Quarter using Inline also





                  * INLINE

                  Month, Quarter
                  Jan, Q1
                  Feb, Q1
                  Mar, Q1
                  Apr, Q2
                  May, Q2
                  Jun, Q2
                  Jul, Q3
                  Aug, Q3
                  Sep, Q3
                  Oct, Q4
                  Nov, Q4
                  Dec, Q4



                  By this way you can create the Quarter for Fiscal Calendar also.


                  2. You can create in the script using "if" condition.


                  Hope this helps!


                  Please get back in case more help required.