How to calculate MTD at script level?

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

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.....

Thanks,

Sandip

Re: How calculate MTD script level

vinieme12 — Sat, 06 May 2017 13:42:40 GMT

Group by Month?

Qlik Community Tip: How to Get Answers to Your Post

Re: How calculate MTD script level

ananyaghosh — Sun, 07 May 2017 06:52:17 GMT

Hi,

I think my post has now enough information to understand my requirement.

Please can you help me now?

Re: How calculate MTD script level

Anil_Babu_Samineni — Mon, 08 May 2017 05:17:54 GMT

Script?

Look at Help for functions:

InYearToDate() for YTD

InMonthToDate() for MTD

topic How to calculate MTD at script level? in QlikView

How to calculate MTD at script level?

Re: How calculate MTD script level

Re: How calculate MTD script level

Re: How calculate MTD script level