Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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)
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')
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)