topic Re: MTD calculation- Need help in QlikView

MTD calculation- Need help

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

Hi,

I am attaching a sample data source and I need MTD calculation Set expression.

I amusing the below expression:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)

and have added date as dimension.

but, it show me only corresponding date's value, not the accumulated value from month start date to corresponding date value.

So, if 1/1/2015 have value 10 and 1/12/2015 have value 20,

then it show 30 for 1/12/2015.

Any help will be appreciated.

Thanks,

Sandip

Re: MTD calculation- Need help

rahulpawarb — Fri, 05 May 2017 07:03:50 GMT

Hello Sandip,

Please refer below link:

Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)

Regards!

Rahul

Re: MTD calculation- Need help

ananyaghosh — Fri, 05 May 2017 09:04:26 GMT

Hi,

You can see from my post that their expression does not work for me if i select (order date) as dimension and if i select other collumn as dimension it just show the last year's value along with previos year's value.

Please help me to solve this situation.

Re: MTD calculation- Need help

rahulpawarb — Fri, 05 May 2017 09:50:48 GMT

Refer attached sample application. If you are still facing issue then let me know...

Regards!

Rahul

Re: MTD calculation- Need help

ananyaghosh — Fri, 05 May 2017 13:21:00 GMT

As i am using the qlikvuew personal edition, i cannot open your qvw file.

So please give me the script and dimension name and set expression used for it.

Re: MTD calculation- Need help

ananyaghosh — Fri, 05 May 2017 17:35:19 GMT

Can u give me the script? Along with dimensiob and expressions?

Re: MTD calculation- Need help

neelamsaroha157 — Fri, 05 May 2017 19:28:41 GMT

Remove First 'date=' from your expression

sum({<Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)

Re: MTD calculation- Need help

neelamsaroha157 — Fri, 05 May 2017 19:31:20 GMT

Also you can use 'Full Accumulation' option in the expression properties.

Re: MTD calculation- Need help

ananyaghosh — Sat, 06 May 2017 09:49:31 GMT

Hi,

I am using the below code with the concept of As Of table and I can't calculate MTD using 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(AsOfDate>AsOfDateMonthStart and MonthStartToDateDiff>0 and MonthDiff =0, 1 ,0) as FlagMTD2;
Load
[Order Date],
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;

Please help me