Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

MTD - LMTD

Hi everyone

I want to calculate MTD and LMTD.

MTD:

When a date is selected and it will look for the Starting date of the Month to the selected date.

Ex: date selected is 14-02-2015 then MTD is 01-02-2015 to 14-02-2015.


LMTD:

When a date is selected and it will look for the Starting date of the Last Month to the selected date.

Ex: date selected is 14-02-2015 then LMTD is 01-01-2015 to 14-01-2015.



6 Replies
shraddha_g
Honored Contributor III

Re: MTD - LMTD

For MTD:

Sum({<Date = {">=$(=max(MonthStart)) <=$(=max(Date)) "} >}  Sales)

For LMTS:

Sum({<Date ={" >=$(=date(addmonths(max(MonthStart), - 1)))<=$(=date(addmonths(max(Date),-1)))"}>} Sales)

Re: MTD - LMTD

Try this:

MTD

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DD-MM-YYYY') & '<=' & Date(Max(Date), 'DD-MM-YYYY'))"}>}Measure)

LMTD

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1), 'DD-MM-YYYY') & '<=' & Date(AddMonths(Max(Date), -1), 'DD-MM-YYYY'))"}>}Measure)

Not applicable

Re: MTD - LMTD

Hi Sunny

This calculation is not work for my app. Result of this formule is zero.

Re: MTD - LMTD

Is your date format DD-MM-YYYY like its used in example above? or is it something else. Make sure to read this thread to understand Dates in Set Analysis

brunobertels
Valued Contributor II

Re: MTD - LMTD

Hi

Try this :

MTD :

YTD

Sum({$<[Date] = {'>=$(=MonthStart(Today()))'}>} [Sales])

LMTD

Sum({$<[Date] = {'>=$(=Date(addmonth(monthstart(Today()),-1)))<$(=Date(addmonth(Today(),-1)))'}>} [Sales])

shraddha_g
Honored Contributor III

Re: MTD - LMTD

also you can try:

For MTD:

Sum({<Date = {">=$(=max(MonthStart)) <=$(=max(Date)) "},Month=,Year= >}  Sales)

For LMTS:

Sum({<Date ={" >=$(=date(addmonths(max(MonthStart), - 1)))<=$(=date(addmonths(max(Date),-1)))"},Month=,Year=>} Sales)