Strange behaviour with AddMonths Function

[helen_pip]

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

[sunny_talwar · Accepted solution]

May be try this

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

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

[sunny_talwar]

Or this

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

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

[vinieme12]

You should be using AddYears()

AddMonths(FactDateNum,-1) as PrevMonth,

AddYEars(FactDateNum,-1) as PrevYear

[swuehl]

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).

[helen_pip]

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