2 Replies Latest reply: May 6, 2017 5:52 AM by sandip Ghosh RSS

    How to use MTD, YTD, WTD calculation in a As-Of table script?

    sandip Ghosh


      I am using the below script: and I need to implement MTD, YTD and WTD calculations. So how to do in the as of table 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(YearDiff=0,'Current Year',If(YearDiff=1,'Previous Year')) as FlagYear;
        [Order Date],
        floor([AsOfDate]) as DateKey,
        [AsOfDate] - [Order Date] as DayDiff,
        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;