Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
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