Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ananyaghosh
Creator III
Creator III

need to do MTD calculation in script level, so how to achieve this?

Hi,

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 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(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
Where
AsOfDate >= [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.....

4 Replies
ananyaghosh
Creator III
Creator III
Author

Please any body can help me?

Anonymous
Not applicable

try the function inmonthtodate()

perhaps something like:

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

ananyaghosh
Creator III
Creator III
Author

Hi,

I am using the below code:

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

and it works for me

Anonymous
Not applicable

InMonthToDate supports only up to 3 parameters, so it should be

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


??!?