Problem in calculating the time periods

[Prabhu]

Hi All,

I have Year(yyyy) and Month(MM) fields in data.

I am trying to calculate the time periods in load script as below:

If(Year=Year(Today()) and Month>=Month(QuarterStart(today())) and Month<=Month(today()),'QTD') as QTD,
if(Year=Year(Today()) and Month<=Month(Today()), 'YTD') as YTD

It is not working. Please help.

[stabben23]

Hi,

you can do it like this in Your calendar, this will gives you "flag" for all periods. %DateKey is Your datefield.

inYeartoDate(%DateKey,today(1),0) * (-1) as YTD, // 1 for year to date
inYeartoDate(%DateKey,today(1),-1) * (-1) as LYTD,// last year to date
inMonthtoDate(%DateKey,today(1),0) * (-1) as MTD, // month to date
inMonthtoDate(%DateKey,today(1),-1) * (-1) as LMTD, // lastmonth to date
inMonthtoDate(%DateKey,today(1),-13) * (-1) as LMLY, //lastmonth last year
inWeek(%DateKey,today(1),0) * (-1) as thisWeek, // this week

InQuarterToDate(%DateKey,QuarterEnd( Today()),0)*-1 as QTDFlag,

[prma7799]

Try Like below

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,

  if(YearDiff=0, 'YTD') as FlagYTD,

  if(AsOfDate>=AsOfDateMonthStart and MonthStartToDateDiff>=0 and MonthDiff =0, 1 ,0) as FlagMTD,

  if((AsOfDate>=AsOfDateWeekStart)  and (WeekStartToDateDiff>=0) and WeekDiff =0, 1 ,0) as FlagWTD,

  if(inmonthtodate([Order Date],[AsOfDate],0,1),1,0) as INMONTHTODATE_FLAG,

  if(InQuarterToDate([Order Date],[AsOfDate], 0,1),1,0) as  FlagQtd