Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

same year same month

hi all,

I am facing one issue regarding same month sales and comparision in last year same month

example:  current month and year is 2017 January   and  I need same comparison for  jan month 2016

how can I achieve this?

what is the sum of sales in jan2017 and  jan2016 .

6 Replies
arulsettu
Master III
Master III

for current year month

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)


last year month


Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum), -12)))<=$(=AddYears(Max(DateNum), -1))"}>} Sales)


for more

https://community.qlik.com/docs/DOC-6163

soniasweety
Master
Master
Author

thanks arul.

But is it possible to achieve by using Addmonths?

arulsettu
Master III
Master III

yes

soniasweety
Master
Master
Author

can you give the expression how it look like?

arulsettu
Master III
Master III

like this

sum({<ShipDate={">=$(=MonthStart(max(ShipDate),-6))<=$(=AddMonths(max(ShipDate),-6))"}>}Sales)

adjust the value to get month

sdmech81
Specialist
Specialist

Hi,

Create variable for max date using addmonths,like below so tht u can use them repetitively  in many places

vMax=Max(Your_Date)

vPrev_yr_month_start=Monthstart(AddMonths((vMax), -12) )

vCurrent_Yr_month_start=Monthstart(vMax)

vPrev_yr_max_date=AddMonths((vMax), -12)

Upon this the expressions are as follows:

Sum({<Year=, Month=, Quarter=, Week=, DateField=, Date={">=$(vCurrent_Yr_month_start)  <=$(vMax)"}>} Sales)


last year month


Sum({<Year=, Month=, Quarter=, Week=, DateField=, Date={">=$(vPrev_yr_month_start )  <=$(vPrev_yr_max_date)"}>} Sales)

Please cross check as I directly typed here only!!!!

Even u can put that vExcludeDate=Year=, Month=, Quarter=, Week=, DateField=       inside variable n use