Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

stevietm
New Contributor III

MTD vs Previous month to date same date

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

1 Solution

Accepted Solutions

Re: MTD vs Previous month to date same date

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

3 Replies

Re: MTD vs Previous month to date same date

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

Highlighted

Re: MTD vs Previous month to date same date

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
Valued Contributor III

Re: MTD vs Previous month to date same date

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