topic Re: same year same month in QlikView

same year same month

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

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 — Wed, 01 Feb 2017 10:31:47 GMT

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

soniasweety — Wed, 01 Feb 2017 10:33:08 GMT

thanks arul.

But is it possible to achieve by using Addmonths?

arulsettu — Wed, 01 Feb 2017 10:36:29 GMT

yes

soniasweety — Wed, 01 Feb 2017 10:39:11 GMT

can you give the expression how it look like?

arulsettu — Wed, 01 Feb 2017 10:48:51 GMT

like this

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

adjust the value to get month

sdmech81 — Wed, 01 Feb 2017 11:08:08 GMT

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