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

[ananyaghosh]

Hi,

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?

tmpAsOfCalendar:
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;
Load
  [Order Date],
  floor([AsOfDate]) as DateKey,
  [AsOfDate],
  [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

Resident
  tmpAsOfCalendar
Where
  AsOfDate >= [Order Date];
 
drop table tmpAsOfCalendar;

[ananyaghosh]

Please help me for the above post.

[ananyaghosh]

Hi,

Please can anybody help me?