Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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


??!?