Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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')

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