topic Re: Strange behaviour with AddMonths Function in QlikView

Strange behaviour with AddMonths Function

helen_pip — Wed, 26 Apr 2017 12:17:50 GMT

Dear Qlikview user

I am creating 2 fields in my script which creates two dates for me:

AddMonths(FactDateNum,-1) as PrevMonth,

AddMonths(FactDateNum,-12) as PrevYear

FactDateNum is created by the following equation:

num(Floor(CLOCK_STOP_OR_SNAPSHOT)) as FactDateNum

I would like to use Aug-16 as an example

PrevYear

AddMonths(FactDateNum,-12) as PrevYear

Will return a values of 31/08/2015 - Which is the desired result

PrevMonth

AddMonths(FactDateNum,-1) as PrevMonth

If a user selects Sep-16 - then this field displays 30/08/2016. I cannot understand where the 31/08/2016 has gone too, if PrevYear is able to display the final day in the month

I thought my dataset may be wrong, but the fact that PrevYear can return a value of 31/08 I wonder if my expression is an issue

Kind Regards

Helen

Re: Strange behaviour with AddMonths Function

sunny_talwar — Wed, 26 Apr 2017 12:20:38 GMT

May be try this

Date(Floor(MonthEnd(FactDateNum, -1))) as PrevMonth,

Date(Floor(MonthEnd(FactDateNum, -12))) as PrevYear

Re: Strange behaviour with AddMonths Function

sunny_talwar — Wed, 26 Apr 2017 12:21:06 GMT

Or this

DayName(MonthEnd(FactDateNum, -1)) as PrevMonth,

DayName(MonthEnd(FactDateNum, -12)) as PrevYear

Re: Strange behaviour with AddMonths Function

vinieme12 — Wed, 26 Apr 2017 12:21:26 GMT

You should be using AddYears()

AddMonths(FactDateNum,-1) as PrevMonth,

AddYEars(FactDateNum,-1) as PrevYear

Re: Strange behaviour with AddMonths Function

swuehl — Wed, 26 Apr 2017 12:22:22 GMT

Try using optional mode =1

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Re: Strange behaviour with AddMonths Function

helen_pip — Wed, 26 Apr 2017 12:24:30 GMT

Thanks Sunny

This has worked for me, as I realise that I was counting back 30 days from September which resulted in 30/08/2016

Thanks

Helen