Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
stevietm
Creator
Creator

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
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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
Specialist III
Specialist III

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

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