topic Re: MTD vs Previous month to date same date in QlikView

MTD vs Previous month to date same date

stevietm — Tue, 01 Aug 2017 12:54:07 GMT

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

Re: MTD vs Previous month to date same date

sunny_talwar — Tue, 01 Aug 2017 12:58:37 GMT

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')

Re: MTD vs Previous month to date same date

Kushal_Chawda — Tue, 01 Aug 2017 12:59:38 GMT

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')

Re: MTD vs Previous month to date same date

rahulpawarb — Tue, 01 Aug 2017 13:02:19 GMT

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

Re: MTD vs Previous month to date same date

HereWeGoAgain — Mon, 24 Jan 2022 07:59:31 GMT

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