getting previous month values from yearmonth column

[rsdhavle]

We have one yearmonth field in a format like 201403, 201402. On the basis of these values we are trying to get values as Mar14,Feb14 etc. When we use function month(now(yearmonth))&year(now(yearmonth)) it gives output as Mar2014 but when we try to calculate previous month by using function month((now(yearmonth-1)))&year(now(yearmonth))  to get output as Feb2014 it gives value as Mar2014 again.

Kindly guide what value should we use so that we get values of previous months like Feb14, Jan14, Dec13 etc..

[Nicole-Smith · Accepted solution]

I would suggest using date functions so it will treat your values as dates instead of text.

Current:

date(date#(yearmonth&'01', 'YYYYMMDD'), 'MMMYY')

Previous:

date(addmonths(date#(yearmonth&'01', 'YYYYMMDD'),-1), 'MMMYY')

[rsdhavle]

Perfect..Thanks Nicole!!

[rsdhavle]

in addition to that lets say we have one revenue field and on the basis of above values we need to calculate Sum(Rev) for particular values like Mar14, Feb14 etc. How can we do that? What would be the correct expression

[jolivares]

There are many ways to do that, but try to a different approach.

Assume that yourDate = 201403

First 201403, this is not a date, change to Date#(yourDate&'01','YYYYMMDD'), now you have a date 2014/03/01

Let's apply the function MonthName(Date#(yourDate&'01','YYYYMMDD')) = Mar 2014 (this is a date),

Now if you want last month you can do this

MonthName(AddMonths(Date#(yourDate&'01','YYYYMMDD'),-1)) = Feb 2014

[Nicole-Smith]

What would the dimension be?  (It can't be the date field if you want to use a separate expression for current and previous.)

[rsdhavle]

I have different dimesnsions lets say client. And for client C1 i need to calculate revenue for currecnt month mar14 and in next column for feb14 and Jan14 etc..just the rquirement is it should start from the latest month in the data and based on that the previous values

[Nicole-Smith]

Create a Pivot Table.

First Dimension: Client

Second Dimension: date(date#(yearmonth&'01', 'YYYYMMDD'), 'MMMYY')

Expression: sum(Revenue)

Then pivot (drag) the second dimension (date column) so that the dates go across the top and create columns.

Example file is attached.