4 Replies Latest reply: May 7, 2017 10:17 AM by Robin Hausdörfer RSS

    need to do MTD calculation in script level, so how to achieve this?

    sandip Ghosh


      I need to do MTD calculation in script level, so how to achieve this?

      Actually I am using the below script:

      Load distinct [Order Date]
      Resident [Order Data];

      Join (tmpAsOfCalendar)
      Load [Order Date] as AsOfDate
      Resident tmpAsOfCalendar; 

      [As-Of Calendar]:
      Load *,
      If(DayDiff=0,'Current Day',If(DayDiff=1,'Previous Day')) as FlagDate,
      If(WeekDiff=0,'Current Week',If(WeekDiff=1,'Previous Week')) as FlagWeek,
      IF(MonthDiff=1 and Day([Order Date]) <= Day(AsOfDate), 'Previous Month',
      IF(MonthDiff=0 and Day([Order Date]) <= Day(AsOfDate), 'Current Month')) as FlagMonth,

      if(MonthStartToDateDiff = DayDiff, 1,0) as FlagMTD,
      If(YearDiff=0,'Current Year',If(YearDiff=1,'Previous Year')) as FlagYear,
      if(YearDiff=0, 'YTD') as FlagYTD,
      if(AsOfDate>AsOfDateMonthStart and MonthStartToDateDiff>0 and MonthDiff =0, 1 ,0) as FlagMTD2;
      [Order Date],
      MonthStart(date([Order Date],'YYYYMMDD')) as Period1,
      floor([AsOfDate]) as DateKey,
      [AsOfDate] as  [AsOfCalDate],
      Week(AsOfDate) AS Week,
      Date(AsOfDate,'YYYYMM') AS YearMonth
      ApplyMap('QuartersMap', Month(AsOfDate), Null()) as Quarter,
      Year(AsOfDate) AS Year
      [AsOfDate] - [Order Date] as DayDiff,
      MonthsStart(1,[AsOfDate],0) as AsOfDateMonthStart,
      [AsOfDate]MonthsStart(1,[AsOfDate],0) as MonthStartToDateDiff,
      Round((WeekStart(AsOfDate)-WeekStart([Order Date]))/7) as WeekDiff
      Year(AsOfDate)*12 + Month(AsOfDate)) - (Year([Order Date])*12 + Month([Order Date])) as MonthDiff,
      Year(AsOfDate)-Year([Order Date]) as YearDiff

      AsOfDate >= [Order Date];

      drop table tmpAsOfCalendar;



      Also I am attaching the data source file . Please......................help me about how to incorporate MTD flag in my script.


      Please help me.....