topic Re: Problem in calculating the time periods in QlikView

Problem in calculating the time periods

Prabhu — Fri, 02 Jun 2017 09:45:40 GMT

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.

Re: Problem in calculating the time periods

stabben23 — Fri, 02 Jun 2017 10:18:49 GMT

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,

Re: Problem in calculating the time periods

prma7799 — Fri, 02 Jun 2017 11:00:33 GMT

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