Discussion Board for collaboration on QlikView Scripting.
I need to do MTD calculation in script level, so how to achieve this?
Actually I am using the below script:
tmpAsOfCalendar:Load distinct [Order Date]Resident [Order Data]; Join (tmpAsOfCalendar)Load [Order Date] as AsOfDateResident 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(MonthStartToDateDiff = DayDiff, 1,0) as FlagMTD,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 FlagMTD2;Load[Order Date],MonthStart(date([Order Date],'YYYYMMDD')) as Period1,floor([AsOfDate]) as DateKey,[AsOfDate],[AsOfDate] as [AsOfCalDate],Week(AsOfDate) AS Week,Date(AsOfDate,'YYYYMM') AS YearMonth, ApplyMap('QuartersMap', Month(AsOfDate), Null()) as Quarter,Year(AsOfDate) AS Year, [AsOfDate] - [Order Date] as DayDiff,MonthsStart(1,[AsOfDate],0) as AsOfDateMonthStart,[AsOfDate] - MonthsStart(1,[AsOfDate],0) as MonthStartToDateDiff,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 WhereAsOfDate >= [Order Date];drop table tmpAsOfCalendar;
Also I am attaching the data source file . Please......................help me about how to incorporate MTD flag in my script.
Please help me.....
Please any body can help me?
try the function inmonthtodate()
perhaps something like:
if(inmonthtodate([Order Date],[AsOfDate],1) as INMONTHTODATE_FLAG
I am using the below code:
if(inmonthtodate([Order Date],[AsOfDate],0,1),1,0) as INMONTHTODATE_FLAG
and it works for me
InMonthToDate supports only up to 3 parameters, so it should be
if(inmonthtodate([Order Date],[AsOfDate],0),1,0) as INMONTHTODATE_FLAG