Find Previous 12 months and future 12 months

[mohan2391]

Hi,

I have MonthYear field in the format YYYYMM (202103). I have to find out which is the 5th last month to current month i.e., 202010 in this case and also need to findout which is the next 12th month i.e., 202203

how can I find this ? It might be a minor logic but I am not aware of it.

Thanks

[qlikeers · Accepted solution]

Hi!

I don't know if I understood correctly, but try this:

Create a table.

Dim.1 -> MonthYear
Dim.2 -> =date(addmonths(makedate(left(MonthYear,4),right(MonthYear,2)),-5),'YYYYMM')
Dim.3 -> =date(addmonths(makedate(left(MonthYear,4),right(MonthYear,2)),12),'YYYYMM')

[Or · Accepted solution]

Ideally, just convert this to a date field and use standard date functions...

MakeDate(Left(MonthYear,4),Right(MonthYear(2)) as MonthYearDate

AddMonths(MonthYearDate,-5)

You can still display these in the format YYYYMM even if they're date fields.

If you have to use numeric values, you'll just have to write out mathematical logic, something along the lines of

if(num(Right(MonthYear,2)<=5, MonthYear-95, MonthYear-5)