MTD vs Previous month to date same date

[stevietm]

Hi All,

So i want to calculate Previous month to date only for the dates available for the current month. Ex if my last date in my data is 2017-07-28 i want to calculate the rev for the same period last month, meaning 2017-06-01 to 2017-06-28.

Currently i am using this expression: num(Sum({$<DATE_KEY={"$(='>=' & MonthStart(Max(DATE_KEY),-1) & '<=' & AddMonths(Max(DATE_KEY),-1))"}>} TOTAL_REV_CALC) ,'R # ##0;R-# ##0')

Is seems to bring the full amount and not for same date range. I understand tat the second part of the expression adds the amount for the max month -1 meaning previous month. Where would i specify to only bring the same dates.

I am not a newbie but haven't worked on qlikview for a while now and only getting back into it now. What am i missing.

Thanks in advance.

Regards STM

[sunny_talwar · Accepted solution]

May be you need the Date function....

Num(Sum({$<DATE_KEY={"$(='>=' & Date(MonthStart(Max(DATE_KEY),-1), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(DATE_KEY),-1), 'YYYY-MM-DD'))"}>} TOTAL_REV_CALC) ,'R # ##0;R-# ##0')

[Kushal_Chawda]

try this

num(Sum({$<DATE_KEY={">=$(=MonthStart(addmonths(Max(DATE_KEY),-1))) <=$(=Monthend(AddMonths(Max(DATE_KEY),-1)))"}>} TOTAL_REV_CALC) ,'R # ##0;R-# ##0')

[rahulpawarb]

May be this:

Num(Sum({$<DATE_KEY={">= $(=MonthStart(Max(DATE_KEY),-1)) <= $(=AddMonths(Max(DATE_KEY),-1))"}>} TOTAL_REV_CALC) ,'R # ##0;R-# ##0')

Regards!

Rahul Pawar

[HereWeGoAgain]

HI @sunny_talwar 

please assist with below

I have created a filter: [LoadDate]>=Monthstart(Today())

this filter only provide current month to date data. I would like use same type of formula, but want to add the whole of previous month as well as current month data. e.g From 2021/12/01 to Date (2022/01/24).

Thanks in advance

Kind Regards