same year same month

[soniasweety]

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 .

[arulsettu]

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]

thanks arul.

But is it possible to achieve by using Addmonths?

[arulsettu]

yes

[soniasweety]

can you give the expression how it look like?

[arulsettu]

like this

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

adjust the value to get month

[sdmech81]

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