Simple Set Analysis Expression

[#1]

I am using the formula below...

=Date(Max([Event YearMonth]-1),'MMM-YYYY')

And it is returning Sep-2014.

However, if I insert any other number (2, 3, 4, etc.) it will still return Sep-2014.

Does anyone know why? I'm trying to show as below.

=Date(Max([Event YearMonth]),'MMM-YYYY') - current month --> Ok

=Date(Max([Event YearMonth]-1),'MMM-YYYY') - previous month --> Ok

=Date(Max([Event YearMonth]-2),'MMM-YYYY') - 2 months ago --> Returns Sep-2014 (same as above)

[jaimeaguilar]

Hi,

Could you please upload a sample of your data in order to help you better?

regards

[Anonymous]

Does this work?

=Date(Max([Event YearMonth])-2,'MMM-YYYY')

[Anonymous · Accepted solution]

[Event YearMonth] is I reckon stored as a numeric date, but as a dual displays as MMM-YYYY.

So subtracting 1, subtracts 1 day and not 1 month.  Which would give you the results you desribe.

Try the AddMonths() function.  I have pasted in the Help Text for it and you can pass a negative value into it as you need.

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

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

[maxgro]

what's the format of   [Event YearMonth]?

could you post some values of this field?

[#6]

That does not work.

[jyothish8807]

Hi DD,'

Try like this:

=month(max(Date))

=month(addmonths(max(Date),-1))  for previous month

Regards

KC

[#8]

This is correct.

It is only subtracting 1 day...

I removed the Date expression and I'm getting below.

=Max(EVENT_YearMonth)-1 - 41882

=Max(EVENT_YearMonth)-2 - 41881

I have to run to a meeting. Let me play with this and I will get back to you. Thank you so much.

[#9]

Thank you everyone for their prompt replies!!!

I was able to use below.

=date(addmonths(max(EVENT_YearMonth),-1),'MMM-YYYY')

=date(addmonths(max(EVENT_YearMonth),-2),'MMM-YYYY')

=date(addmonths(max(EVENT_YearMonth),-3),'MMM-YYYY')

[#10]

Thank you for your advice Bill!

I used the formulas below... this will catch the rollover in terms of year, correct?

For example, if they current year is Jan. 2015, the below formulas will capture Dec. 2014, Nov. 2014?

=date(addmonths(max(EVENT_YearMonth),-1),'MMM-YYYY')

=date(addmonths(max(EVENT_YearMonth),-2),'MMM-YYYY')