3 Replies Latest reply: Feb 26, 2018 6:32 AM by pradnya nemade RSS

    Fiscal Year YTD,MTD,WTD,QTD

    pradnya nemade

      i want to find  YTD,MTD,WTD,QTD using Fiscal Year,Fiscal Month,Fiscal Week,Fiscal Quarter

      Please give Solution for Following Data.

      I need to count(ID) for fiscal year,Fiscal Month,Fiscal Week,Fiscal Quarter

      Below is the sample date and id .Pls help asap

      <   

      IDDATE
      E112/06/2017 04:45 PM
      E212/07/2017 01:15 PM
      E329/07/2017 09:15 AM
      E429/07/2017 02:25 PM
      E521/07/2017 06:50 AM
      E613/07/2017 07:40 PM
      E701/12/2017 06:30 AM
      E802/12/2017 08:00 AM
      E901/12/2017 07:40 AM
      E1001/12/2017 01:00 PM
      E1103/12/2017 01:10 PM
      E1203/12/2017 04:10 PM
      E1304/12/2017 04:15 PM
      E1401/12/2017 03:05 PM
      E1501/12/2017 06:00 AM
      E1603/12/2017 03:40 PM
      E1704/12/2017 05:00 PM
      E1805/12/2017 01:50 PM
      E1903/01/2018 05:50 AM
      E2003/01/2018 07:00 AM
      E2103/01/2018 02:25 AM
      E2203/01/2018 02:40 AM
      E2301/02/2018 10:35 AM
      E2402/02/2018 10:35 AM
      E2503/02/2018 10:35 AM
      E2604/02/2018 10:35 AM
      E2705/02/2018 10:35 AM
      E2806/02/2018 10:35 AM
        • Re: Fiscal Year YTD,MTD,WTD,QTD
          omar bensalem

          1st :

          In this table; create a date field :

           

          YourTable;

          Load * ,

          date(Date#(Subfield(DATE,' ',1),'DD/MM/YYYY')) as DateField

          from source;

           

          Then create a master Calendar as follow:

          QuartersMap: 

          MAPPING LOAD  

          rowno() as Month, 

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

          AUTOGENERATE (12); 

           

          Temp: 

          Load 

                         min(DateField) as minDate, 

                        max(DateField) as maxDate 

          Resident YourTable

           

          Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

          Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

          DROP Table Temp; 

           

          TempCalendar: 

          LOAD 

                         $(varMinDate) + Iterno()-1 As Num, 

                       Date($(varMinDate) + IterNo() - 1) as TempDate 

                         AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

          MasterCalendar: 

          Load 

                         TempDate AS DateField

                         week(TempDate) As Week, 

                         Year(TempDate) As Year, 

                        Month(TempDate) As Month, 

                        Day(TempDate) As Day, 

                         YeartoDate(TempDate)*-1 as CurYTDFlag, 

                         YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                         inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                         date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                         ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                         Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                         WeekDay(TempDate) as WeekDay 

          Resident TempCalendar 

          Order By TempDate ASC; 

          Drop Table TempCalendar; 


          Then please refer to this link to create ur YTD MTD WTD QTD expressions (I tried to explain how to do it step by step)

          YTD, MTD issue