Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello Sandip,
Please refer below link:
Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)
Regards!
Rahul
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.
Refer attached sample application. If you are still facing issue then let me know...
Regards!
Rahul
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.
Hi
Can u give me the script? Along with dimensiob and expressions?
Remove First 'date=' from your expression
sum({<Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)
Also you can use 'Full Accumulation' option in the expression properties.
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