topic Re: Add Months in QlikView

Add Months

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

Hi,

Is there a way to restrict addmonths from taking previous month.

E.g: addmonths('01-02-2017',-3) should give '01-01-2017' not '01-12-2016'

Regards

Or — Wed, 08 Nov 2017 08:08:58 GMT

AddMonths(01-FEB-2017,-3) shouldn't give January 1 2017 OR December 1 2016. It should give November 1 2016. You may want to clarify your goal.

rahulsingh12 — Wed, 08 Nov 2017 08:11:24 GMT

Yeah Meant that only,

My only concern is it should not give 2016 but if months are going beyond the current month then should give the minimum month i.e. for that year

Regards

Mark_Little — Wed, 08 Nov 2017 08:13:29 GMT

Hi,

Is this a restriction you are trying to add for some calculation or mentioned above, it would 01/11/2016?

If you want to make sure it does go back a year wou would need something like.

IF(YEAR(ADDMONTHS(DATE,-3)) <> YEAR(ADDYEARS(DATE,-1)), YEAR(ADDMONTHS(DATE,-3)), YEARSTART(DATE))

Mark

Or — Wed, 08 Nov 2017 08:37:50 GMT

RangeMax(YearStart(YourDate),AddMonths(YourDate,-3)) should do the trick.

rahulsingh12 — Wed, 08 Nov 2017 09:28:35 GMT

I will never get the min date for the selection here.

I need to get the l3m data here and this will give me max of the date I would need to get the min as well.

Regards,

Rahul

Or — Wed, 08 Nov 2017 09:34:58 GMT

You didn't ask for the minimum. You asked for "Three months ago, unless that's last year, in which case January 1st of this year".

rahulsingh12 — Wed, 08 Nov 2017 10:08:22 GMT

I tried something like this:

sum({<Description={'Actual'},MonthYear={">=$(=vL3MLYMinYear)<=$(=vL3MLYMaxYear)"}>}[Volume(HL)])

where vL3MLYMinYear=

IF(MONTH(DateNew)<=2,DATE(YearStart(AddYears(DATE(DateNew,'DD-MM-YYYY'),-1)),'DD-MM-YYYY'),

IF(MONTH(DateNew)>2,

DATE(RANGEMin(AddYears(DATE(DateNew,'DD-MM-YYYY'),-1),AddYears(AddMonths(DATE(DateNew,'DD-MM-YYYY'),-2),-1)),'DD-MM-YYYY')))

vL3MLYMaxYear=

DATE(RANGEMax(AddYears(DATE(DateNew,'DD-MM-YYYY'),-1),AddYears(AddMonths(DATE(DateNew,'DD-MM-YYYY'),-2),-1)),'DD-MM-YYYY')

But the some of sales comes as 0 in this case.

Regards,